18

I want to use a prepared statement in which the passed-in parameters are for the ORDER BY and LIMIT clauses, like so:

$sql = 'SELECT * FROM table ORDER BY :sort :dir LIMIT :start, :results';
$stmt = $dbh->prepare($sql);
$stmt->execute(array(
     'sort'  => $_GET['sort'], 
     'dir'  => $_GET['dir'], 
     'start'  => $_GET['start'],
     'results' => $_GET['results'],
     )
    );

But $stmt->fetchAll(PDO::FETCH_ASSOC); returns nothing.

Can someone point out what's the wrong thing I am doing? Can it be done? If not,what should I reference for a complete list of clauses where parameters can be used?

beldaz
  • 4,299
  • 3
  • 43
  • 63
user198729
  • 61,774
  • 108
  • 250
  • 348

4 Answers4

20

After using :

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

I got the message :

Uncaught exception 'PDOException' with message '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 ''0', '10'' at line 1

So, when you use an array for execute, it consider your inputs as string which is not a good idea for LIMIT 

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT * FROM table ORDER BY :sort :dir LIMIT :start, :results";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':start', $_GET['start'], PDO::PARAM_INT);
$stmt->bindParam(':results', $_GET['results'], PDO::PARAM_INT);
$stmt->bindParam(':sort', $_GET['sort']);
$stmt->bindParam(':dir', $_GET['dir']);
$stmt->execute();

$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($data);
Arkh
  • 8,416
  • 40
  • 45
  • I never see such declaration that parameters can't be used in `limit` and `order` clauses,can you provide some links for reference?Is there a complete list in which parameters can be used? – user198729 Apr 21 '10 at 15:06
  • Seems like I'm wrong, only table / column names can't be parameterized. Your problem comes from your array keys in which you forgot the : (':sort' => $_GET['sort']). – Arkh Apr 21 '10 at 15:19
  • Nope,`:` is optional(it still returns nothing after adding `:`).You can see this here if you pull down enough: http://www.php.net/manual/en/pdostatement.execute.php – user198729 Apr 21 '10 at 15:22
  • But pdo said: *do not include the user-input directly in the query*,how should I do it then? http://www.php.net/manual/en/pdo.prepare.php – user198729 Apr 21 '10 at 15:43
  • Read the edited version, it's not using the user input directly, but binding it using bindValue. – Arkh Apr 21 '10 at 16:09
  • I think they are the same thing. – user198729 Apr 22 '10 at 03:44
  • Then let's use bindParam instead of bindValue. – Arkh Apr 22 '10 at 09:56
  • It's still not working,reporting *check the manual that corresponds to your MySQL server version for the right syntax to use near 'table ORDER BY 'id' 'DESC' LIMIT 0, 20'* – user198729 Apr 22 '10 at 12:55
  • I can't reproduce this when I use things like `$_GET['dir'] = 'ASC';`. What do you use to populate your $_GET array ? – Arkh Apr 22 '10 at 13:39
  • You can simply reproduce it using MySQL,what DBMS are you using? – user198729 Apr 22 '10 at 16:04
  • Mysql 5.1 and php 5.3. How do you populate your $_GET array ? – Arkh Apr 22 '10 at 16:23
7

Prepared statements allow the DBMS to generate a query plan for your query before actually executing the query for your supplied parameters. Changing the fields for ORDER BY requires a different query plan, because ordering you data in different ways can drastically affect how the DBMS might choose to get the data: for instance, certain indexes may help in one case but not in another. For this reason the ORDER BY fields should form part of the SQL string passed into the prepare() method, rather than being bound to the query prior to execute().

As for the LIMIT clause, it's not clear whether its parameters would affect the query plan, so these may be bound later, possibly depending upon your DBMS. According to this SO answer it should be allowed.

Community
  • 1
  • 1
beldaz
  • 4,299
  • 3
  • 43
  • 63
3

You can't bind a parameter to specify a language keyword or a field name - it has to be replacing a literal. Therefore, your limit values I think are fine, but your order by is not. It will be best for you to manually replace sort and dir in the string. Escape them but don't use the DB tools to do so, since they aren't string literals. Basically ensure no special characters are present.

Bob Gettys
  • 1,194
  • 1
  • 8
  • 12
  • I replaced `:sort :dir` with `:sss :xxx` but still not working. – user198729 Apr 21 '10 at 14:25
  • 1
    That's not what I meant. It depends on your DBMS, it appears. With MySQL, for example, you cannot use bind parameters for keywords or names. A user at php.net discovered such as well: http://www.php.net/manual/en/pdo.prepare.php#71127. If you're using a different DBMS, however, then disregard my answer. – Bob Gettys Apr 21 '10 at 21:02
2

Although this question is rather old, I think it might still be of interest. For me it worked after I

  1. used bindParam in combination with PDO::PARAM_INT like suggested before
  2. converted the variable content into an integer value by invoking intval()

The relevant part of the code then looks like this:

    $stmt->bindParam(':start', intval($_GET['start']), PDO::PARAM_INT);
    $stmt->bindParam(':number', intval($_GET['number']), PDO::PARAM_INT);

Without using intval() I also received the error 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 ''0', 10' at line 1

olik79
  • 1,402
  • 1
  • 13
  • 16