Proper Way to create Queries using MySQL With an Eye to Standards and Security

Night4554

Two seperate Questions here:

First off, assume that we're going to check the query for illiict statements after we form it.

Which of these is the most gerally accepted way to form a query statement in PHP.

$query="SELECT username FROM " . $tblpre . "users WHERE id = '".$_SESSION['id']."' AND anotherval = '".$var."' LIMIT 1";
$query="SELECT username FROM " . $tblpre . "users WHERE id = '$_SESSION[id]' AND anotherval = '$var' LIMIT 1";
//OTHER

Now assume that we're using a specially made query($foo) function in place of mysql_query that will check the passed statement for bad things and handle any error reporting, as well as return the result of the query.

In my experience until recently I always worked on servers where magic_quotes_gpc has been On. Meaning that there was no real reason for me to check the submits.

Now in my stupor, I thought I could do a check like the following inside my query($foo) function.

if(!get_magic_quotes_gpc())
	{
	$foo=addslashes($foo);
	}

In case you missed where this goes wrong, examine the following:

$query="SELECT * FROM " . $tblpre . "photos WHERE id = '$tpi'";
$result=query($query);
//SQL Query: SELECT * FROM tableprefix_photos WHERE id = /'123/'
//Die
//Note: I know the slashes go the other way but the boards aren't letting me put them in

Not a situation I care for. So what would be a solution for checking the completely-formed statement inside the query($foo) function that would be efficient, upwards compatible, and secure?

Please keep in mind the extremely frustrating magic_quotes_sybase setting. The answers given will affect what goes into the Gallery System (see sig) and I'm trying to make it as secure and cross-platform as possible.

ahundiak

Check incoming data before build your sql statement. Otherwise, you are really kind of screwed. You could feed the completed statement through a parser but it would be difficult to actually fix any error. You may as well let the statement execute and check the return codes.
Be careful what you ask for. Understand that the whole notion of escaping certain characters is a mysql/pgsql concept and is NOT standard sql. It's all nicely described in the mysql manual in the non-standard section. It's a real shame that php decided to use slashes by default. It can be a rude awakening when developers discover they have learned non-standard sql.
The only thing you really need to do to text data is to "double up" the single 's. In other words, change O'Dulls to O''Dulls before adding it to your query. Basically what the sybase_gpc does.

In my code, I check all incoming data and if gpc is on then I strip the slashes out. I much rather work with the real data and not some mucked up data. Read the comments in the manual on this for more information.

There is a wide spread myth that gpc/addslashes some how protects or adds security to your site. I have yet to actually see a real example.
Adding quotes around numeric data being inserted is not standard sql either.
Finally, I usually use a simple query generator class. It cut's down on syntax errors and improves readability.

Night4554

Is anyone else able to weigh in on this subject?