POST variables in mysql query

[JasonDFR]

I am inserting POST, GET, and SESSION variables into mysql queries like this:


$q2 = "UPDATE `contest_answers`
SET `answer` = '$q_2_ans'
WHERE `ans_id` = {$_POST['ans_2_id']} LIMIT 1 "

Is this the "best practice" or is there a better way to do it?

(validation has been done on the POST variable)

Thanks!

Jason

[rangana]

Ensure you've escaped special characters (http://us.php.net/mysql_real_escape_string) already:


mysql_real_escape_string($_POST['ans_2_id'])

[JasonDFR]

Yes, good advice. I had escaped the special characters.

Use of the curly brackets in the query was my main concern.

Is there a "best practice" for putting POST, GET, or SESSION variables into a mysql query?

Thanks!

J

[Twey]

Personally, I like sprintf():
mysql_query(sprintf('UPDATE contest_answers SET answer = '%s' where ans_id = %d',
$q_2_ans,
$_POST['ans_2_id']))
or die(mysql_error());

[Twey]

Personally, I like sprintf():
mysql_query(sprintf('UPDATE contest_answers SET answer = \'%s\' where ans_id = %d',
$q_2_ans,
$_POST['ans_2_id']))
or die(mysql_error());

[JasonDFR]

I had not thought of that. What is the advantage to using sprintf?

Thanks Twey.

Jason

[Twey]

Well for a start you've got a little validation for free — there's no way that %d can be a string, for example. Conjointly, it increases the readability of the query by showing the query separately, without all the clumsy parameter-preparing code getting in the way.

Of course, if you have them available, PDO prepared statements (http://www.php.net/manual/en/pdo.prepared-statements.php) are preferable by far, since they take care of all escaping, delimiting, and other assorted nonsense for you, as well as providing you with many devices by use of which you can greatly neaten your code.

[JasonDFR]

Cool! Thanks a lot Twey. PDO looks great. You know of any good tutorials?

J

[gwesco]

Along the same line, I am trying to create a function to allow me to update a database when a user selects a quantity in a form.

Here's my function so far:


<?php

$QTY = $_POST['qty'];
Print $QTY;
echo '<P>'; // Test to see if qty is being passed (It is)

function updateDbase () {

// Connects to your Database
$db = DB::connect('mysql://username:password@localhost/my_inventory');
if (DB::isError($db)) { die ("Can't connect: " . $db->getMessage()); }

// Set up automatic error handling
$db->setErrorHandling(PEAR_ERROR_DIE);

$db->query("UPDATE inventory SET InStock = InStock - ( ) where inventory . PartNumber =123456");

mysql_close();

}
?>

My problem is getting the variable into the () in the query. Apparently the code needs to be a little more complex than this? When I plug a number into the (), the database updates correctly so I know everything is working up to that point.

Thanks for any help. I am learning PHP and am not a master of MySql yet. ;-)

[rangana]

It doesn't appear to be "MySql" query for me.

I'm uncertain if I totally understand you, but try:


$db->query("UPDATE inventory SET InStock = InStock - ( ) where inventory . PartNumber =".$QTY."\"");

[forum_amnesiac]

I think this is what he means Rangana


$db->query("UPDATE inventory SET InStock = InStock - (".$QTY." ) where inventory . PartNumber =123456");

Apologies if I'm wrong

[gwesco]

OK, this is what I finally got to work:

$db->query("UPDATE inventory SET InStock = InStock - (" . $_POST["qty"] . ") where inventory . PartNumber =123456");

Now each time the function is called, it decrements the quantity InStock by the amount that was passed from the POST form.

I think it is essentially what you posted but using the $_POST without setting a variable $QTY = $_POST["qty"] first.

Thanks for the help! :)