0

Im having some trouble getting my SQL query to 'insert into' my database, is it allowed to use variables as table name, field name, and values?

Here my code:

$nameOfDBFromA = "vagtplanA" . $_GET["from"];

$flytnedToQ1 = $con->prepare("SELECT * FROM $nameOfDBToA WHERE ansatId='$_GET[ansatId]' ORDER BY id DESC");
$flytnedToQ1->execute();
$flytnedTo1 = $flytnedToQ1->fetch();

$nameOfFieldToA1 = "a" . $_GET["to"] . "1";
$nameOfFieldToA2 = "a" . $_GET["to"] . "2";
$nameOfFieldToA3 = "a" . $_GET["to"] . "3";
$nameOfFieldToA4 = "a" . $_GET["to"] . "4";
$nameOfFieldToA5 = "a" . $_GET["to"] . "5";
$nameOfFieldToA6 = "a" . $_GET["to"] . "6";
$nameOfFieldToA7 = "a" . $_GET["to"] . "7";

$redigeringsTidspunkt = date("j M Y");

$flytnedTA = $con->prepare(
         "INSERT INTO $nameOfDBFromA 
                (ansatId, edit, $nameOfFieldToA1, $nameOfFieldToA2, 
                 $nameOfFieldToA3, $nameOfFieldToA4, $nameOfFieldToA5, 
                 $nameOfFieldToA6, $nameOfFieldToA7) 
          VALUES($_GET[ansatId], $redigeringsTidspunkt, 
                 $flytnedTo1[$nameOfFieldToA1], $flytnedTo1[$nameOfFieldToA2], 
                 $flytnedTo1[$nameOfFieldToA3], $flytnedTo1[$nameOfFieldToA4], 
                 $flytnedTo1[$nameOfFieldToA5], $flytnedTo1[$nameOfFieldToA6], 
                 $flytnedTo1[$nameOfFieldToA7]) ") 
          or die(mysql_error());
$flytnedTA->execute();

SOLVED! I just put my arrays into it own variable

$intoVarToA1 = $flytnedTo1[$nameOfFieldToA1];
$intoVarToA2 = $flytnedTo1[$nameOfFieldToA2];
$intoVarToA3 = $flytnedTo1[$nameOfFieldToA3];
$intoVarToA4 = $flytnedTo1[$nameOfFieldToA4];
$intoVarToA5 = $flytnedTo1[$nameOfFieldToA5];
$intoVarToA6 = $flytnedTo1[$nameOfFieldToA6];
$intoVarToA7 = $flytnedTo1[$nameOfFieldToA7];
Improve this question
5
  • Im suspecting it might be the arrays in the values section? Commented Aug 15, 2016 at 21:14
  • you haven't quoted ANY of your values, so almost 99.999999% certain you've got an sql injection problem. And since mysql_*() has no prepared statements, you're probably ALSO mixing mysql apis, which is also not going to work. mysql_error() can only report on errors caused by the mysql_*() functions, NOT mysqli or pdo. Commented Aug 15, 2016 at 21:16
  • Sorry that i didn't post more of my code in proper context. The quotes in values doesnt solve the problem, ive tried that a thousand times, with and without. This should work, there's something with the syntax i dont know about. All my variables have what they need to contain. have checked them so many times. Commented Aug 15, 2016 at 21:24
  • Your script is at risk of SQL Injection Attack Have a look at what happened to Little Bobby Tables Even if you are escaping inputs, its not safe! Use prepared statement and parameterized statements Commented Aug 15, 2016 at 21:50
  • Since you're using mysqli, you should use $flytnedTA->bind_param() rather than substituting variables into the query. Commented Aug 15, 2016 at 22:09

4 Answers 4

Reset to default
1

You shouldn't substitute variables into the query, you should use bind_param() to provide parameter values for the prepared query.

$flytnedTA = $con->prepare(
         "INSERT INTO $nameOfDBFromA 
                (ansatId, edit, $nameOfFieldToA1, $nameOfFieldToA2, 
                 $nameOfFieldToA3, $nameOfFieldToA4, $nameOfFieldToA5, 
                 $nameOfFieldToA6, $nameOfFieldToA7) 
          VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?) ") 
          or die(mysqli_error($con));
$flytnedTA->bind_param("sssssssss", $_GET[ansatId], $redigeringsTidspunkt, 
                 $flytnedTo1[$nameOfFieldToA1], $flytnedTo1[$nameOfFieldToA2], 
                 $flytnedTo1[$nameOfFieldToA3], $flytnedTo1[$nameOfFieldToA4], 
                 $flytnedTo1[$nameOfFieldToA5], $flytnedTo1[$nameOfFieldToA6], 
                 $flytnedTo1[$nameOfFieldToA7]);
$flytnedTA->execute();

You also need to call mysqli_error($con), not mysql_error().

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

One of your mistakes is when you want to access a value in an array inside of a string, you can't do:

"$flytnedTo1[$nameOfFieldToA1]"

You have to do it like this:

"{$flytnedTo1[$nameOfFieldToA1]}" // use curly brackets
Improve this answer

4 Comments

Just tried that too, the same result. I'm sure you're right about the syntax, but there must be something else going on.
Could u echo the output of input to the prepare() function. That should help us to identify the issue. I.e $test ="INSERT INTO $nameOfDBFromA (ansatId, edit, $nameOfFieldToA1, $nameOfFieldToA2, $nameOfFieldToA3, $nameOfFieldToA4, $nameOfFieldToA5, $nameOfFieldToA6, $nameOfFieldToA7) VALUES ('$_GET[ansatId]', '$redigeringsTidspunkt', '{$flytnedTo1[$nameOfFieldToA1]}', '{$flytnedTo1[$nameOfFieldToA2]}', '{$flytnedTo1[$nameOfFieldToA3]}', '{$flytnedTo1[$nameOfFieldToA4]}', '{$flytnedTo1[$nameOfFieldToA5]}', '{$flytnedTo1[$nameOfFieldToA6]}', '{$flytnedTo1[$nameOfFieldToA7]}') "; echo $test;
here: INSERT INTO vagtplanA1 (ansatId, edit, a21, a22, a23, a24, a25, a26, a27) VALUES ('5', '', '', '', '', '', '', '', '')
$redigeringsTidspunkt seem to have a problem too? that doesn't make any sense.
0

If you have variables like that, you can insert data into db like below in php.

 $first_name = mysqli_real_escape_string($link, $_POST['firstname']);
    $last_name = mysqli_real_escape_string($link, $_POST['lastname']);
    $email_address = mysqli_real_escape_string($link, $_POST['email']);
    $sql = "INSERT INTO persons (first_name, last_name, email_address) VALUES    ('$first_name', '$last_name', '$email_address')";
Improve this answer

2 Comments

That method is at risk of SQL Injection Attack Have a look at what happened to Little Bobby Tables Even if you are escaping inputs, its not safe! Use prepared statement and parameterized statements
Thank you Riggs ! I looked Prepared Statements. Generally I code queries in yii2 Active Record.
0

Is not a good practice put _GET or _POST variables directly on query, use mysqli_real_escape_string to clear the value in variable.

The array values are not parsed directly in strings, you must enclose the expression in {}:

For this: " $flytnedTo1[$nameOfFieldToA3] " replace with: "'{$flytnedTo1[$nameOfFieldToA3]}'" , the result value also need to enclosed by '' singlequoes for sql string value.

$flytnedTA = $con->prepare("INSERT INTO $nameOfDBFromA (ansatId, edit, $nameOfFieldToA1, $nameOfFieldToA2, $nameOfFieldToA3,
    $nameOfFieldToA4, $nameOfFieldToA5, $nameOfFieldToA6, $nameOfFieldToA7) 
    VALUES({$_GET['ansatId']}, '$redigeringsTidspunkt', '{$flytnedTo1[$nameOfFieldToA1]}', '{$flytnedTo1[$nameOfFieldToA2]}', 
    '{$flytnedTo1[$nameOfFieldToA3]}', '{$flytnedTo1[$nameOfFieldToA4]}', '{$flytnedTo1[$nameOfFieldToA5]}', 
    '{$flytnedTo1[$nameOfFieldToA6]}', '{$flytnedTo1[$nameOfFieldToA7]}') ") or die(mysql_error());
Improve this answer

2 Comments

Just tried it no luck.. Maybe its because i have variables as field names, ansatId, edit, $nameOfFieldToA1, $nameOfFieldToA2, $nameOfFieldToA3, $nameOfFieldToA4, $nameOfFieldToA5, $nameOfFieldToA6, $nameOfFieldToA7?
Can you show me the result of this string? echo "INSERT INTO $nameOfDBFromA (ansatId, edit, $nameOfFieldToA1, $nameOfFieldToA2, $nameOfFieldToA3, $nameOfFieldToA4, $nameOfFieldToA5, $nameOfFieldToA6, $nameOfFieldToA7) VALUES({$_GET['ansatId']}, '$redigeringsTidspunkt', '{$flytnedTo1[$nameOfFieldToA1]}', '{$flytnedTo1[$nameOfFieldToA2]}', '{$flytnedTo1[$nameOfFieldToA3]}', '{$flytnedTo1[$nameOfFieldToA4]}', '{$flytnedTo1[$nameOfFieldToA5]}', '{$flytnedTo1[$nameOfFieldToA6]}', '{$flytnedTo1[$nameOfFieldToA7]}') "

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.