PHP DB - SQL injection & prepared statements

Author

SQL injection & prepared statements

Gerard Samuel

2004-06-25, 3:55 pm

Just looking for opinions on sql injection while using prepared statements.
I've read at the oci8 extention that content should not be "escaped"
---
Do not use magic_quotes_gpc or addslashes() and oci_bind_by_name()
simultaneously as no quoting is needed and any magically applied quotes will
be written into your database as oci_bind_by_name() is not able to
distinguish magically added quotings from those added intentionally.
---

I've also been told something similar to this from the author of the ODBTP php
extention (http://odbtp.sourceforge.net/).

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.

Thanks.

Hans_l

2004-06-25, 3:55 pm

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

Sponsored Links