-30

I'm updating some old PHP code and ran across an issue I don’t completely understand. In the old days of mysql_* functions you could include a variable in your SQL query like:

$query = "SELECT * FROM table $limit";

Where $limit = "LIMIT 0,50";. Thus the complete query was 

$query = "SELECT * FROM table LIMIT 0,50";

And everything worked fine. However, with PDO prepared statements and named parameters, this type of simple substitution doesn't seem possible unless you break up the limit statement. For example:

$stmt = $conn->prepare('SELECT * FROM table :myLimit');
$stmt->execute(array(':myLimit'=>' LIMIT 0,50'));

Results in the error:

ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

But if I change that query to the following so that the LIMIT is broken down further:

$stmt = $conn->prepare('SELECT * FROM table LIMIT :start,:end ');
$stmt->execute(array(':start'=>0,':end'=>50));

It works great.

  • So why doesn't using :myLimit as the named parameter and array(':myLimit'=>' LIMIT 0,50') as the value work?
  • What are the rules for using named parameters, and how do they differ from the simple variable substitution in the SQL string that the old mysql_* functions could use?

The PDO pages on php.net are a little ambiguous when it comes to what can and can’t be used as named parameters and I was looking for something a little more in-depth than what I found:

  • You must include a unique parameter marker for each value you wish to pass in to the statement
  • You cannot use a named parameter marker of the same name twice in a prepared statement.
  • You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.

I'm currently using PHP 5.1.6

j08691
  • 204,283
  • 31
  • 260
  • 272
  • 1
    Someone else correct me if I'm wrong but when you bind values it basically sanitizes them so that it is just a value for comparison. Since Limit is a reserved word it probably sanitizes that. – Pitchinnate Jul 02 '13 at 15:59
  • 1
    no. placeholders can only insert values. NOT sql key words. `LIMIT ?,?` is possible, but not the entire substatement as a single placeholder. – Marc B Jul 02 '13 at 16:00
  • @MarcB - yes I discovered that through trial and error. Are there any docs that explain the specifics of the restrictions on parameters? Php.net seems to gloss over this. – j08691 Jul 02 '13 at 16:01
  • Prepared statements is not like copy&paste. It's really more like separate arguments for a function, with the prepared statement being the function. – deceze Jul 02 '13 at 16:02
  • @Pitchinnate there is no such thing like "sanitize" and surely PDO does not do it. – Your Common Sense Jul 02 '13 at 16:05
  • parameters can only be used to specify values, not anything sql-related. e.g. `$sql = "? ? ? ?"; $args = array('SELECT', '*', 'FROM', 'foo');` is invalid, because none of those 4 'values' you're passing in are actual values. they're core structural components of the query. – Marc B Jul 02 '13 at 16:05
  • @YourCommonSense sorry sanitize isn't the right word – Pitchinnate Jul 02 '13 at 16:06
  • I think you need to upgrade PHP. I just tried this with PHP 5.4.16 and it works fine to use parameters in the `LIMIT` clause. Unless you're using MySQL 4.0, which I won't rule out if you're using such an old version of PHP. – Bill Karwin Jul 02 '13 at 16:28
  • @BillKarwin - Unfortunately upgrading PHP isn't an option at the moment. MySQL version 5.0.95. – j08691 Jul 02 '13 at 16:29
  • I don't have PHP 5.1 around to test, but I suspect your only option is to interpolate integers into your SQL string before calling prepare(). Just be careful to force the values to be integers, and you should be as safe from SQL injection as parameters would be. – Bill Karwin Jul 02 '13 at 16:32

2 Answers2

9

why doesn't using :myLimit as the named parameter and array(':myLimit'=>' LIMIT 0,50') as the value work?

Because prepared statements are for data only

What are the rules for using named parameters, and how do they differ from the simple variable substitution in the SQL string that the old mysql_* functions could use?

The rules are simple: you can use parameters (of either type) for the data only

I'm currently using PHP 5.1.6

Man. You know, you are a bit late with upgrade. Around ten years or so.

You cannot use a named parameter marker of the same name twice in a prepared statement.

That's true. In order to use this feature you'll have to turn the emulation mode on, which would make it inconvenient for other queries.

You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.

That's true. Again because [native] prepared statement is for data literals only

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
0

When you bind a value, you can only include a value not any part of the query other than a value to be checked against.

Bound values are used to protect against query manipulation so if you could change the query or add extra mysql commands into the bound parameter then this would negate the whole point of binding a value

For example you can bind a name or number to check if something equals that value, you cannot bind a condition

Anigel
  • 3,435
  • 1
  • 17
  • 23