[GLLUG] Retrieving a row from mysql

[Szymon Machajewski]

Jeremy,

I have to disagree with you on using the function always.

It will depend greatly on your database.  What if you have a database
where you retrieve data not only with PHP, but with Crystal Reports and
other tools.  What about the escape characters then ?
You will see on the ODBC report: O\'Brien

So generally if you control both input and output from the database and
you know it will be PHP retrieving data you could use the escape
function.
But most of all you always have to include data validation functions.
This will be not only to escape characters.

For example you should have a sql_safe() function where you would
define the escape characters you choose to use.  Then you would use
$comments = sql_safe($comments) to reset each value before using in a
query.
What works sometimes is simply replacing single quotes with back ticks.
 They look fine on the web page and in Crystal.

So there are no blanket solutions and there is always a trade off.




Sincerely,

Szymon Machajewski MCSD, CNA, MySQL Core
Grand Rapids Community College

Open Sourceror


>>> Jeremy Bowers <jerf at jerf.org> 2/17/2004 10:34:24 AM >>>
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.
_______________________________________________
linux-user mailing list
linux-user at egr.msu.edu 
http://www.egr.msu.edu/mailman/listinfo/linux-user