CODEWITHSUNDEEP
phpmysqlsqlselect
Ammer Naber
Ammer Naber

Reputation: 41

Error in SQL syntax, check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY category ASC' at line 1

Site has been working fine for years and all of a sudden I am getting this error. Any help from the experts would be greatly appreciated.

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 'ORDER BY category ASC' at line 1

Here is the code in question:

// SQL injection attack prevention function
$unit_Recordset1 = "";
if (isset($_GET['unit'])) {
  $unit_Recordset1 = GetSQLValueString($_GET['unit'], "text");
}
$category_Recordset1 = "";
if (isset($_GET['category'])) {
  $category_Recordset1 = GetSQLValueString($_GET['category'], "text");
}
else $_GET['category'] = "";

// Query builder that create single or multiple AND query
$sql = "SELECT * FROM documents WHERE ";
if(!empty($unit_Recordset1)) {$sql .= " unit = $unit_Recordset1 AND ";}
if(!empty($category_Recordset1)) {$sql .= " category = $category_Recordset1 AND ";}
// Remove the last AND
$sql = substr($sql, 0, -4);
if(!empty($category_Recordset1)) $sql .= " ORDER BY title ASC";
else $sql .= " ORDER BY category, title ASC";


// Query for left nav DISTINCT category values
$sqlnav = "SELECT DISTINCT category FROM documents WHERE unit = $unit_Recordset1 ORDER BY category ASC";


mysql_select_db($database_local, $local);

$Recordset1 = mysql_query($sql, $local) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

$Recordset2 = mysql_query($sqlnav, $local) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);

Upvotes: 0

Views: 1092

Answers (2)

Indra Kumar S
Indra Kumar S

Reputation: 2934

Your $sqlnav query should be like this

 SELECT DISTINCT category FROM documents WHERE unit = '$unit_Recordset1' ORDER BY category ASC

encapsulate variable $unit_Recordset1 with single quotes

Upvotes: 0

Mureinik
Mureinik

Reputation: 311163

There are flows where $unit_Recordset1 may be empty. In which case, the following statement:

$sqlnav = "SELECT DISTINCT category FROM documents WHERE unit = $unit_Recordset1 ORDER BY category ASC";

Will evaluate to:

SELECT DISTINCT category FROM documents WHERE unit = ORDER BY category ASC

Which, of course, isn't valid SQL. You need to add a check against this case too, something down the lines of:

$unitClause = "";
if(!empty($unit_Recordset1) {
    $unitClause = "WHERE unit = $unit_Recordset1 ";
}
$sqlnav = "SELECT DISTINCT category FROM documents $unitClause ORDER BY category ASC";

Upvotes: 1

Related Questions