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 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
– Marc B, 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. — Jesper Olsen
– Jesper Olsen, 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 — RiggsFolly
– RiggsFolly, 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. — Barmar
– Barmar, Commented Aug 15, 2016 at 22:09

Barmar · Accepted Answer · 2016-08-15 22:12:10Z

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

Ömer A. · Accepted Answer · 2016-08-16 01:29:16Z

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, 2016 at 1:29

Ömer A.

6776 silver badges17 bronze badges

answered Aug 15, 2016 at 21:24

printfmyname

98115 silver badges32 bronze badges

4 Comments

Jesper Olsen Over a year ago

Just tried that too, the same result. I'm sure you're right about the syntax, but there must be something else going on.

printfmyname Over a year ago

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;

Jesper Olsen Over a year ago

here: INSERT INTO vagtplanA1 (ansatId, edit, a21, a22, a23, a24, a25, a26, a27) VALUES ('5', '', '', '', '', '', '', '', '')

Jesper Olsen Over a year ago

$redigeringsTidspunkt seem to have a problem too? that doesn't make any sense.

Shaig Khaligli · Accepted Answer · 2016-08-15 21:18:29Z

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, 2016 at 21:18

Shaig Khaligli

5,5675 gold badges26 silver badges33 bronze badges

2 Comments

RiggsFolly Over a year ago

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

Shaig Khaligli Over a year ago

Thank you Riggs ! I looked Prepared Statements. Generally I code queries in yii2 Active Record.

Ivan Cachicatari · Accepted Answer · 2016-08-15 21:24:07Z

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

answered Aug 15, 2016 at 21:24

Ivan Cachicatari

4,3143 gold badges24 silver badges41 bronze badges

2 Comments

Jesper Olsen Over a year ago

Just tried it no luck.. Maybe its because i have variables as field names, ansatId, edit, $nameOfFieldToA1, $nameOfFieldToA2, $nameOfFieldToA3, $nameOfFieldToA4, $nameOfFieldToA5, $nameOfFieldToA6, $nameOfFieldToA7?

Ivan Cachicatari Over a year ago

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]}') "

Collectives™ on Stack Overflow

Insert into with variables php mysql

4 Answers 4

Comments

4 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related