Is this the correct syntax for MySQL query?
$sql = "UPDATE goods SET stock = $_post["number1"], WHERE item = $_POST["number2"];
//$sql = "UPDATE goods SET stock = stock + 113 WHERE id = 2";
if(!($result = mysql_query($sql,$dblink))) {
print "Fail: query";
exit;
where the code is in bold: can I use $_POST like that or does it need a something before it?
pr0gr4mm3r posted this at 03:40 — 6th December 2007.
He has: 1,502 posts
Joined: Sep 2006
The syntax is technically correct, but you should never use $_GET or $_POST variables directly in queries.
If I was a malicious user, I could send whatever I want to that script. I could make $_POST['number2'] equal...
2; DROP TABLE goods;'...which would drop that table. Simple yet devastating. There are plenty of other malicious strings that could be injected into a query.
Simple way to fix it: run all values you take from a user through the mysql_real_escape_string() function.
<?php
$number1 = mysql_real_escape_string($_POST['number1'];
$number2 = mysql_real_escape_string($_POST['number2'];
/* then run the query */
$sql = \"UPDATE goods SET stock = $number1, WHERE item = $number2;
?>
JeevesBond posted this at 07:49 — 6th December 2007.
He has: 3,956 posts
Joined: Jun 2002
pr0gramm3r writes wise words. Take heed. PHP can be rather insecure if used improperly.
calculator posted this at 13:44 — 6th December 2007.
They have: 40 posts
Joined: Nov 2007
if the posted values can only be number I would add an extra layer of security by checking if the $_post are numeric using the built-in php is_numeric() function.
if(is_numeric($_POST['number1']) && is_numeric($_POST['number2'])){
$number1 = mysql_real_escape_string($_POST['number1']);
$number2 = mysql_real_escape_string($_POST['number2']);
}
/* then run the query */
$sql = "UPDATE goods SET stock = $number1, WHERE item = $number2;
I know it's not necessary, but I think it then becomes a coding practice.
As an additional comment, if you know what calues can be chosen, ie values from a drop down menu, use the switch/case to make sure that the value entered is valid.
And as a final comment - after that I shut up
- read Chris Shiflett excellent Essential PHP Security book.
JeevesBond posted this at 05:10 — 8th December 2007.
He has: 3,956 posts
Joined: Jun 2002
Good point calculator, you can also cast the value as an int:
<?php
$number1 = (int) mysql_real_escape_string($_POST['number1'];
$number2 = (int) mysql_real_escape_string($_POST['number2'];
/* then run the query */
$sql = \"UPDATE goods SET stock = $number1, WHERE item = $number2;
?>
That'll make sure they're numerics. Both methods will work.
a Padded Cell our articles site!
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.