CODEWITHSUNDEEP
phpjavascripthtml
lodkkx
lodkkx

Reputation: 1173

php sql statement formation

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.

Upvotes: 0

Views: 126

Answers (5)

codercake
codercake

Reputation: 1017

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.

Upvotes: 1

Frank
Frank

Reputation: 459

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

Upvotes: 1

Johnny Craig
Johnny Craig

Reputation: 5002

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]

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 691635

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

Upvotes: 2

Rob W
Rob W

Reputation: 348972

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

Upvotes: 1

Related Questions