Preventing SQL injections with ADOdb for PHP

Databases
1

Hi,

I’ve just come across ADOdb for PHP and find it very useful.

The thing I can’t figure out is how to prevent SQL injections with ADOdb.

Can someone who is familiar with ADOdb help me make safe and secure queries on my DB?

Thanks,
Eric

2

A pretty standard way of intercepting SQL injection attacks is by using prepared statements. This can be done in a number of different data abstraction layers. You can find ADODB’s docs on prepared statements here.

Hope this helps.

3

alternatively (but not necessarily better) use the Quote() function provided by ADOdb.

4

OK, I checked this out and it didn’t make much sense to me. It says, “Prepares (compiles) an SQL query for repeated execution”. What if I’m just doing 1 query?

Also, I tried to use the example on that page but I just don’t get it. How do I echo results with that query?

I tried this and got a little closer but not close enough. When I used Quote, it added extra quotes and broke the query.

Below are the queries that I’ve tried and their results.

First Query:

$id1 = '11945-455972285';
$result = $db->GetAll('select name from products where id=?', array($id1) );
foreach($result AS $row) {
	echo $row['name'];
}

First Debug:

select name from products where id='11945-455972285'

Second Query:

$id2 = '11945-455972286';
$result = $db->GetAll('select name from products where id=?', $db->Quote($id2) );
foreach($result AS $row) {
	echo $row['name'];
}

Second Debug:

select name from products where id='\\'11945-455972286\\''
5

you’re already using SQL injection safe queries by using the ?. no further action is required.

6

Ahh, thank you for clarifying that for me.

I have 1 last question concerning querying with values from the user.

I want the ORDER BY to be changeable as well.

I tried this but it didn’t order the results based on price.

$sql = 'SELECT name, price FROM products WHERE category=? AND merchant=? ORDER BY ? DESC';
$cat = 'futons-bed-11945';
$mid = '11945';
$order_by = 'price';
$values = array($cat,$mid,$order_by);
$result = $db->GetAll($sql, $values);
foreach($result AS $row) { 
	echo $row['name'] . ', ' . $row['price'] . '<br>';
}

DEBUG:
SELECT name, price FROM products WHERE category=‘futons-bed-11945’ AND merchant=‘11945’ ORDER BY ‘price’ DESC

Is this kind of bind (bind on the ORDER BY) possible?

Thanks,
Eric

7

not using parameters, no. you’ll have to use simple string concatenation.

8

Thanks for your help.

1 last question about SQL injections… Is it possible for someone to do an SQL injection on the part of the query after the WHERE’s and AND’s?

For example:
ORDER BY sql_injection here LIMIT 10

9

i can’t think of a way off the top of my head, but i don’t suggest not guarding against it. in the case of an ORDER BY, i suggest matching the user-supplied input against an array of known-good values. this shouldn’t be too difficult since you already know all of the valid column names.

10

OK, thanks for your help on this!

Eric