php sql statement formation

Question

I am trying to take the entire post array and put each value into a select statement that will update the previous value. I am having trouble doing this in php with string escaping.

This is the SQL statement I am trying to pass:

UPDATE test1l.testtable SET age=106, weight=345, WHERE id=11

So the submission page first puts the $_POST array into a variable. Then I try to set the statement up so that each post variable is in the right place.

$dataArray = $_POST;
$Sql =" UPDATE test1l.testtable SET age=$dataArray['age'],weight=$dataArray['weight'] WHERE id=$dataArray['id']"

The single and double quotes are messing my statement up. Also, I have a custom function that is character escaping these strings so as to help stop injection.

Remove the commas before WHERE: UPDATE test1l.testtable SET age=106, weight=345 WHERE id=11 — Rob W
– Rob W, Commented Dec 16, 2011 at 21:25
I am having problems putting this into a php string. Like where to put what quotes. That was just a copy pasta error — lodkkx
– lodkkx, Commented Dec 16, 2011 at 21:26
"I have a custom function that is character escaping these strings so as to help stop injection." Why use a custom function? There already builtin functions for doing this. — Mark Byers
– Mark Byers, Commented Dec 16, 2011 at 21:28
$query = "UPDATE test1l.testtable SET age=$dataArray['age'],weight=$dataArray['weight'] WHERE id=$dataArray['id']" — Jasoneer
– Jasoneer, Commented Dec 16, 2011 at 21:29

JB Nizet · Accepted Answer · 2011-12-16 21:31:47Z

2

Use concatenation :

'update ... SET age = ' . $dataArray['age'] . ', weight = ...'

And make sure to validate and properly escape your post attributes to avoid SQL injection. Or even better: use prepared statements to be sure. See http://php.net/manual/fr/mysqli.prepare.php

answered Dec 16, 2011 at 21:31

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

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

Comments

Rob W · Accepted Answer · 2011-12-16 21:28:36Z

1

Store the verified user input (mysql_real_escape_string for unknown input) in a variable, then use sprintf as shown below. This line of code will never break, unless some of the variables are not defined, or if the database information is incorrect.

$query = sprintf("UPDATE test1l.testtable SET age='%s', weight='%s' WHERE id='%s'", $age, $weight, $id);

answered Dec 16, 2011 at 21:28

Rob W

350k87 gold badges811 silver badges683 bronze badges

Comments

Frank · Accepted Answer · 2011-12-16 21:45:34Z

1

something like this would be safer:

$dataArray = $_POST; 
$Sql =" UPDATE test1l.testtable SET age=?, weight=? WHERE id=?";
$Statement = $mysqli->prepare($Sql);
$Statement->bind_param('iii', $dataArray['age'], 
                              $dataArray['weight'], 
                              $dataArray['id']);
$Statement->execute();

answered Dec 16, 2011 at 21:45

Frank

4593 silver badges9 bronze badges

Comments

codercake · Accepted Answer · 2011-12-16 22:14:19Z

If you are going with a format like this:

$Sql =" UPDATE test1l.testtable SET age=$dataArray['age'],weight=$dataArray['weight'] WHERE id=$dataArray['id']"

It should be done like this:

$Sql ="UPDATE test1l.testtable SET age={$dataArray['age']},weight={$dataArray['weight']} WHERE id={$dataArray['id']}";

You have to surround complex variables like array and objects with {} when working inside double quotes.

$string = "print {$object->param} and {$array['key']}";

Also don't forget your query is dealing with all integer fields - once you get to string fields in the db like varchar, char, text - you're query must surround these in single quotes like this:

$Sql ="UPDATE test1l.textfield SET textfield='{$dataArray['age']}' WHERE id={$dataArray['id']}";

Also as mentioned any variable based on user input intended for part of an SQL statment should be using either parametrized statements or the function mysql_real_escape_string.

As a note this is where you must change to a string building format more like:

$sql= 'UPDATE test1l.testtable SET age='.mysql_real_escape_string($dataArray['age']).', weight='.mysql_real_escape_string($dataArray['weight']).' WHERE id='.mysql_real_escape_string($dataArray['id']);

And ideally you would extract this into a reusable function that would sanitize all fields intended for the database with mysql_real_escape_string, so you don't have to keep making such ugly/unreadable sql strings.

Johnny Craig · Accepted Answer · 2011-12-16 21:38:44Z

0

i do it like this

$mysqli->query("UPDATE test1l.testtable SET age='".$dataArray['age']."',
            weight='".$dataArray['weight']."' WHERE id='".$dataArray['id']."' ")

just incase you were unaware, you can remove the ticks from inside the brackets: $dataArray[age]

edited Dec 16, 2011 at 21:38

answered Dec 16, 2011 at 21:29

Johnny Craig

4,9902 gold badges28 silver badges28 bronze badges

Collectives™ on Stack Overflow

php sql statement formation

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related