WHERE CLAUSE & ORDER BY IN MYSQL PHP - Parse error: syntax error, unexpected '' ' (T_CONSTANT_ENCAPSED_STRING), expecting ')'

Question

Having scoured these boards I can't seem to find a response which gives me the solution to the error I'm seeing with the below statement - I believe I have the order of this correct but am likely missing something simple, I want to display only rows with an entry in 'postcode' and order the columns by the 'sort_order' parameter - see code below...

if ($result = mysqli_query($conn,'SELECT * FROM temptable
 where postcode<>''
 ORDER BY ' .  $column . ' ' . $sort_order)) {

change it to **if ($result = mysqli_query($conn,"SELECT * FROM temptable where postcode<>'' ORDER BY " . $column . '" " . $sort_order)) {** you cant use ' as start/end of string and as empty string in one string or you must escape — Bernd Buffen, Jan 08 '22 at 16:48
It depends -- Is that inside single-quotes? Double quotes? A "here-doc"? — Rick James, Jan 09 '22 at 06:34

score 0 · Answer 1 · answered Jan 08 '22 at 17:34

You either need to escape the inner single quotes or use double quotes for the query string.

if ($result = mysqli_query($conn,'SELECT * FROM temptable
     where postcode<>\'\'
     ORDER BY ' .  $column . ' ' . $sort_order)) {

or

if ($result = mysqli_query($conn,"SELECT * FROM temptable
     where postcode <> ''
     ORDER BY $column $sort_order")) {

Make sure you are validating the values being passed in for $column and $sort_order, otherwise this will be a very simple SQL injection attack vulnerability. Better still, look into using prepared statements when building SQL queries which concatenate strings based on user input.

Prepared Statements

WHERE CLAUSE & ORDER BY IN MYSQL PHP - Parse error: syntax error, unexpected '' ' (T_CONSTANT_ENCAPSED_STRING), expecting ')'

1 Answers1