ansaurus

Question

Preventing a database from rejecting user input

Answer 1

+1 A:

I think the solution is way easier: You are inserting the user input directly, therefore the ' break your SQL query. (This is already a SQL injection!) So you need to escape those. But I don't know which method ADOdb provides for this. It's mysql_real_escape_string for mysql_ and PDO::quote for PDO.

Furthermore: Please don't use $_REQUEST, use $_POST instead. And even more please don't use register_globals, it's insecure! Use $_POST instead.

nikic 2010-07-22 21:55:00

I'm using $_REQUEST in this instance because it's easier to test. :-)

James 2010-07-22 22:01:36

Answer 2

+4 A:

You're using ADODB, yet you aren't using the prime feature of almost every single database abstraction library that has ever existed: placeholders / prepared statements.

Your queries are failing because things aren't being properly escaped. Using placeholders adds escaping automatically. Read up on the Param method, which will insert the right type of placeholder for the database you're using. (It looks like you're using MSSQL, and I have no idea whether it uses ? as a placeholder or not.)

Instead of

$sql = "SELECT * FROM " . $table . " WHERE dbnameId='" . $dbname . "'";
$sth = $db->Execute($sql);

You want something like:

$sql = "SELECT * FROM $table WHERE dbnameId = " . $db->Param('dbname');
$psh = $db->Prepare($sql);
$sth = $db->Execute($psh, array( $dbname ));

(This is untested and may be subtly wrong, please read the linked manual page for a little more information. Have I mentioned that ADODB is awful?)

Please keep in mind that ADODB is awful and was designed in the PHP4 era. Please consider using something more modern, like PDO.

Charles 2010-07-22 21:55:26

+1 for actually knowing the library (instead of Googling parameter binding for it like I did xD) and recommending a better alternative :)

Matchu 2010-07-22 22:01:40

I've never touched ADODB in my life, I googled for the parameter binding method as well. I'd actually prefer your solution, as I don't really care for having to do a separate prepare. Cross +1'd. ;)

Charles 2010-07-22 22:04:27

I'd assumed that ADODB was escaping things automatically regardless of the method used to build the statement. It's *magic!* (tm). Thanks for setting me straight; I'm using binding and switching things over to PDO now.

James 2010-07-23 14:59:47

Answer 3

+2 A:

It's not aggressive filtering to prevent SQL injection; it's that there's no SQL injection prevention whatsoever here. The single quote in the CSS is being interpreted as a single quote in the query, causing a syntax error.

I haven't used ADOdb before, but it looks like it supports parameter binding, which will allow you to form prettier-looking queries and have them work as expected (with no risk of SQL injection).

$db->Execute('UPDATE foo SET bar = ?, baz = ?', array("it's", "working"));

As the database sees it, the above is equivalent to the following:

$db->Execute("UPDATE foo SET bar = 'it\'s', baz = 'working'");

The syntax is correct, you avoid errors and injection, and it's much nicer to work with, anyway :)

Matchu 2010-07-22 21:57:20

ansaurus

tags:

views:

answers:

Preventing a database from rejecting user input

related questions