Re: SQL injection & prepared statements

Gerard Samuel wrote: <snip>

So Im guessing that this is how "prepared" statements are done on other databases. After being trained to do it one way (always escaping "bad" content), Im being shown to do it the other way, and Im looking for any suggestions you may have.

Yes, the idea with prepared statements is that the database (or transport layer, etc.) knows how to properly escape the values. This is possible in PHP abstraction layers also. For example, in Creole [1], which emulates prepared statements for most databases that it supports, you also don't need to escape things yourself (in fact doing so will cause problems): $stmt = $conn->prepareStatement('SELECT * FROM mytable WHERE col=?"); $stmt->setString(1, $raw_string_data); Internally, the string will be escaped using functions like mysql_escape_string() -- or whatever native function is for your rdbms. Other layers like MDB [2] also support emulated prepared statements, which provide the same sort of behind-the-scenes escaping. I think PreparedStatements are a better solution than building SQL yourself, because it's easy to forget that you can't trust the types of variables in PHP. For example, consider this example: $sql = "UPDATE mytable SET textfield = '" . mysql_escape_string($text) . "' WHERE id = " . $id; Sure, the $text is being quoted, but what about $id ! Mistakes like this are easy to make because at least when I look at it my first instinct is "yeah id is an integer, therefore a number, doesn't need to be escaped". Of course it could just as well be "1 OR 1 = 1" which would update every row in the db... Prepared statements and prepared statement emulators help make that impossible -- and the resulting SQL will be much easier to read. -Hans [1] http://creole.phpdb.org [2] http://pear.php.net/package/MDB