Why it shows me error when preparing a sentence in PHP with MySQLi

Question

I'm creating a sentence prepared in PHP, and I run into a rare syntax error, I do not know if it is breaching any of MySQL or why I show that error

The syntax is as follows, I want to sort by row and by ascending or descending type and limit the results

$query = "SELECT * FROM myTable ORDER BY ? ? LIMIT? ,?"

if($conn->prepare($query)){ .. } // error

The error is

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 '? LIMIT ? , ?' at line 1

If you execute said statement in MySQL, it correctly throws the results

@Sachin the linking is not done, if it is not prepared correctly the error is not the binding. — DarkFenix, Sep 27 '18 at 04:24
After the order by you give two "?" and after the limit need space between limit and "?" — Sachin, Sep 27 '18 at 04:26
Table and Column names cannot be replaced by parameters in PDO. https://stackoverflow.com/questions/2542410/ — Sachin, Sep 27 '18 at 04:34

score 1 · Accepted Answer · answered Sep 27 '18 at 03:57

1

Parameters to ORDER BY are not values, and cannot be parametrised. One is a column reference, the other is a keyword.

answered Sep 27 '18 at 03:57

Amadan

191,408
23
240
301

What would be the best way to add the order by in terms of security to the query. Concatenating? – DarkFenix Sep 27 '18 at 04:02
Unfortunately, yes. Make _very_ sure your concatenated snippet is not user-submitted, or is properly validated. – Amadan Sep 27 '18 at 04:04

score 0 · Answer 2 · answered Sep 27 '18 at 04:06

0

For example do like this and try. $query = "SELECT * FROM myTable ORDER BY column_name LIMIT 0,10";

answered Sep 27 '18 at 04:06

Dheeraj Verma

38
7

Why it shows me error when preparing a sentence in PHP with MySQLi

2 Answers2