MariaDB error with query

Question

I'm getting following error when executing query .

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''0', '25'' at line 1

here is the query

   $sqlData = 'SELECT * FROM users WHERE u_id_id = :UID_ID ORDER BY :ORDER_BY :ORDER_TYPE limit :START, :DATA_LENGTH';

        $params = array(
            ":UID" => $uId,
            ":ORDER_BY" => $orderBy,
            ":ORDER_TYPE" => $orderType,
            ":START" => $start,
            ":DATA_LENGTH" => $length
        );
      $queryData = \registry::getDBHandler()->prepare($sqlData);


  $queryData->execute($params);
var_dump($queryData->execute($params));

note

here is the var dum output of paramas

array (size=5)
  ':UID' => string '66' (length=2)
  ':ORDER_BY' => string 'id' (length=2)
  ':ORDER_TYPE' => string 'asc' (length=3)
  ':START' => string '0' (length=1)
  ':DATA_LENGTH' => string '25' (length=2)

I don't think you can use prepared variables with ORDER BY or LIMIT. — gen_Eric, Dec 29 '16 at 17:39
@RocketHazmat : is there any other way to do this ? pls advice — , Dec 29 '16 at 17:40
Where are your `$params` coming from? Especially the `$orderBy`, `$orderType`, `$start` and `$length`. You're going to have to *manually* append those values to the query, so you're also going to have to correctly escape them. Or rather, do something like this: http://stackoverflow.com/a/2543144 Where you have a hard-coded set of values you can use and only use it if it's in the list (or for the LIMIT just make sure they are numbers). — gen_Eric, Dec 29 '16 at 17:41
@ Rocket Hazmat its from my dataTable $orderByColumnIndex = $formData['order'][0]['column']; $orderBy = $formData['columns'][$orderByColumnIndex]['data']; $orderType = $formData['order'][0]['dir']; $start = $formData['start']; $length = $formData['length']; — , Dec 29 '16 at 17:44
Ok, so those aren't entered in by the user? Then you'll need to *manually* append them into the query string and only have `$uId` in your `$params` array. — gen_Eric, Dec 29 '16 at 17:45
In fact, you can use all of them except for `ASC` in a prepared statement. — elenst, Dec 29 '16 at 17:48
@Rocket Hazma " you meant something like this 'SELECT * FROM users WHERE u_id_id = :UID_ID ORDER BY $orderBy $orderType limit $start,$length' ? pls advice — , Dec 29 '16 at 17:56
@Tharuperera: Yes, except use *double quotes* instead of single quotes. — gen_Eric, Dec 29 '16 at 17:57
I rolled back the question and answers since it did not make sense without the statement. If you need to delete the details, at least show us the query. — Rick James, Dec 30 '16 at 19:35

score 0 · Accepted Answer · answered Dec 29 '16 at 18:02

Prepared statements let you bind variables to the WHERE (and I think SELECT) clauses of an SQL query. Unfortunately, they do not let you bind to the ORDER BY or LIMIT (or FROM) clauses. For that, you will need to manually append to the string.

Since those values are not being entered by the user, you should be safe from SQL injection if you just do:

$sqlData = "SELECT * FROM users WHERE u_id_id = :UID_ID ORDER BY $orderBy $orderType LIMIT $start, $length";

_{(Note the double quotes around the string)}

And then your $params array would just be:

$params = array(":UID" => $uId);

If you are worried about SQL injection, then you can use the following to help with that:

For your ORDER BY, you can make sure that your $orderBy is in a hard-coded list of fields and reject it if it is not.
For $orderType, just simply ensure it is equal to either "asc" or "desc" (possibly ignoring case).
With $start and $length, make sure they are integers. You can also try to use intval() to convert them if need be.

If you follow these rules, then it should be safe to append these variables into your SQL query. Since $uId is part of the WHERE, you can use the prepared variable for it and that is fine.

MariaDB error with query

note

1 Answers1