0

Possible Duplicate:
How do I use pdo's prepared statement for order by and limit clauses?

I'm using PDO as it has been recommended as the way to go when it comes to PHP database connections. But at the same time I am a obsessed with securing my queries to make sure that my system is as safe as possible from hackers.

PDO and prepared statements is a great way to go, but I have a couple of issues with it. I have a custom filtering system that demands I build a query manually. For example, this:

$query=$pdo->prepare('SELECT * FROM log WHERE username=?');
$result=$query->execute(array($_GET['username']));

This works and everything is great - PDO deals with making sure the $_GET variable does not harm my query.

But what to do when I need to escape other things? For example, if I have this situation, where I want to return just five records:

$query=$pdo->prepare('SELECT * FROM log WHERE username=? LIMIT 5');
$result=$query->execute(array($_GET['username']));

This, again, works. But what if the limit values also come from $_GET? How to escape it?

To do this I first figured that I have to build the query manually and use PDO::quote() method, like this:

$query='SELECT * FROM log WHERE username=? LIMIT '.$pdo->quote($_GET['limit']);

But this did not work, since it placed quotes around the limiter which breaks the query.

Is there a proper way of escaping with PDO the way mysql_real_escape_string() worked? Since the latter never put quotes around the resulting variable, but I'm unable to stop this behavior with quote().

Alternative would be to build my own escaper, but that kind-of defeats the purpose of using PDO prepared statements to begin with (prepared statements themselves always put quotes around values).

EDIT: I also tried casting the value as integer in quote, like this:

$pdo->quote((int)$value,PDO::PARAM_INT);

But it -still- places quotes around it. Same with intval().

Why is PDO so actively suggested and recommended for use if I have to do even primitive things like that custom? I really don't want to write a sanitizing method for cases like this and hope that nothing breaks or is compromised.

Community
  • 1
  • 1
kingmaple
  • 4,200
  • 5
  • 32
  • 44
  • Also Related: http://stackoverflow.com/questions/2683576/how-do-i-use-pdos-prepared-statement-for-order-by-and-limit-clauses – Brad Christie Apr 26 '12 at 14:04

2 Answers2

2

You are concerned about integer values. As $_GET is always string, you can turn it into an integer with a cast or the %d format of sprintf:

$query = $pdo->prepare(
    'SELECT * FROM log WHERE username=? LIMIT ' . (int) $_GET['page']
);

$query = $pdo->prepare(
    sprintf('SELECT * FROM log WHERE username=? LIMIT %d', $_GET['page'])
);

If you actually need a string, the quote() function you already wrote about is appropriate.

hakre
  • 193,403
  • 52
  • 435
  • 836
  • Sorry but this is not really a solution, since I am building the query dynamically. It will be an awful hack to do it this way. I am actually close to dumping PDO and returning to mysql_*() functions that did not have this problem with mysql_real_escape_string(). I need to have quote() deal with both integer and non-integer values, but it only works with strings, not integers. PDO::PARAM_INT is useless in this context. – kingmaple Apr 26 '12 at 14:10
  • @kristovaher: What's the issue? You can first run something sprintf/vsprintf style to build the actually query and then use parametrized queries. I don't understand. – hakre Apr 26 '12 at 14:12
  • Because that is not properly escaping. I would like to make PDO::quote() work like mysql_real_escape_string() worked, but at the moment it puts quotes around the value even if it is an integer and PDO::PARAM_INT is set. – kingmaple Apr 26 '12 at 14:13
  • quote is about quoting. In case of limit you need a literal number. That's two things. – hakre Apr 26 '12 at 14:14
  • Just cast it to int as in the first example. Integers are inherently safe, and do not require quoting. – Mike Caron Apr 26 '12 at 14:19
  • Then I suppose PDO does not have mysql_real_escape_string() type escaping method? Alright, I'll resort to just converting everything to integers. – kingmaple Apr 26 '12 at 14:20
  • 1
    All PDO::PARAM_INT does is make sure that an INT is being bound to the placeholder. There is still an abstracted layer of process when the DB call is made that translates the query into the db driver of choice...which I have noticed still has some hiccups. For literal numbers, you need to build the query string manually, then bind the values to the placeholders. The advantage is that if you change your db type (possible with MySQL, considering) you don't have to refactor *all* your DB calls in PHP. – Malovich Apr 26 '12 at 14:23
-3

This is a great beginner's tutorial. Edumacate thyself.

Why you should be using PHP's PDO...

Blake
  • 2,294
  • 1
  • 16
  • 24
  • I've been developing for years. The article you linked is useless in this context and does not deal with the question posted here. – kingmaple Apr 26 '12 at 14:11
  • There's a difference between "developing for years" and "knowing everything about PDO" Having resources is beneath you. – Blake Apr 26 '12 at 14:14
  • http://stackoverflow.com/faq#etiquette -- Lacking. – Blake Apr 26 '12 at 14:23