Insert into with variables php mysql

Question

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];

you haven't quoted ANY of your values, so almost 99.999999% certain you've got an [sql injection](http://bobby-tables.com) 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. — Marc B, Aug 15 '16 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. — Jesper Olsen, Aug 15 '16 at 21:24
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared statement and parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Aug 15 '16 at 21:50
Since you're using `mysqli`, you should use `$flytnedTA->bind_param()` rather than substituting variables into the query. — Barmar, Aug 15 '16 at 22:09

score 1 · Answer 1 · answered Aug 15 '16 at 22:12

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().

score 0 · Answer 2 · answered Aug 15 '16 at 21:18

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')";

answered Aug 15 '16 at 21:18

Shaig Khaligli

4,955
5
22
32

1

That method is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared statement and parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) – RiggsFolly Aug 15 '16 at 21:51
Thank you Riggs ! I looked Prepared Statements. Generally I code queries in yii2 Active Record. – Shaig Khaligli Aug 16 '16 at 06:42

score 0 · Answer 3 · answered Aug 15 '16 at 21:24

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());

Just tried it no luck.. Maybe its because i have variables as field names, ansatId, edit, $nameOfFieldToA1, $nameOfFieldToA2, $nameOfFieldToA3, $nameOfFieldToA4, $nameOfFieldToA5, $nameOfFieldToA6, $nameOfFieldToA7? — Jesper Olsen, Aug 15 '16 at 21:30
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]}') "` — Ivan Cachicatari, Aug 15 '16 at 22:40

score 0 · Accepted Answer · edited Aug 16 '16 at 01:29

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

edited Aug 16 '16 at 01:29

Ömer An

600
5
16

answered Aug 15 '16 at 21:24

printfmyname

983
15
30

Just tried that too, the same result. I'm sure you're right about the syntax, but there must be something else going on. – Jesper Olsen Aug 15 '16 at 21:38
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; – printfmyname Aug 15 '16 at 21:51
here: INSERT INTO vagtplanA1 (ansatId, edit, a21, a22, a23, a24, a25, a26, a27) VALUES ('5', '', '', '', '', '', '', '', '') – Jesper Olsen Aug 15 '16 at 22:14
$redigeringsTidspunkt seem to have a problem too? that doesn't make any sense. – Jesper Olsen Aug 15 '16 at 22:15

Insert into with variables php mysql

4 Answers4