[GLLUG] Retrieving a row from mysql

[Jeremy Bowers]

Shawn Paige wrote:
> query = mysql_query("SELECT * FROM " . $tablename . "
> WHERE name= '" . $name . "' AND accountnum= " .
> $accnum);

I figured somebody would mention this by now, but nobody has.

Never do this.

*Always* use mysql_real_escape_string around your parameters;

http://us2.php.net/manual/en/function.mysql-real-escape-string.php

There are two reasons. One, you may entirely accidentally end up 
including active SQL characters in your $name variable; imagine someone 
enters O'Brian. This sort of thing happens all the time and it's better 
to prevent it.

Second, you open yourself up to SQL injection attacks; suppose someone 
enters their name as "'; drop table [yourtablename];'"; MySQL may drop 
your table. (Of course they can do other things, and depending on your 
implementation may be able to acquire other information.

Even if you don't care about the second (trusted users only, etc.), in 
my experience the first reason is reason enough to never directly insert 
strings into an SQL query. (You can directly insert numbers but only 
after making *damned sure* they are numbers. This may be easier in PHP 
then Perl, which is a little too friendly in the string<->number 
converting.) You'll end up using an apostrophe sooner or later, usually 
in a demo to somebody ;-)

I don't do PHP and am greatly surprised that PHP's mysql interface 
doesn't seem to have a printf-like command like every other scripting 
language I've seen. That lets you insert placeholders, pass in the 
values, and let the library do the quoting, like this:

mysql_query("SELECT * FROM MyTable WHERE name = %s AND accountnum = %s",
             $name, $accountnum);

This is safe and relatively convenient.