Click here to Skip to main content
15,072,776 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have the below code, but mysql query keeps given an error, I am trying to use the header column to sort ascending/descending but doesn't work.

Gives the following message:

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


any help would be great.

What I have tried:

<pre lang="PHP">
<pre>function process() {
	global $DatabaseServer, $DatabaseUser, $DatabasePswd, $DatabaseName, $AgentPin;
	$cn=mysqli_connect($DatabaseServer, $DatabaseUser, $DatabasePswd, $DatabaseName);
	$sql = "SELECT pin.username, pendingindex.* FROM pin join sorttest ON pin.AgentID = sorttest.AgentID join pendingindex ON sorttest.pendreckey = pendingindex.pendreckey ORDER BY" . $sort . " " . $sort_order;


// sets id as default sort (you can set something else than id)
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'pendreckey';

// define sort order
$sort_order = 'asc';
if(isset($_GET['sort_by'])) {
 if($_GET['sort_by'] == 'asc') {
  $sort_order = 'desc';
 } else {
  $sort_order = 'asc';
 }
}

// query table
$query  = mysqli_query($cn,$sql) or die(mysqli_error($cn));
$keys   = mysqli_fetch_array($query, MYSQLI_ASSOC);

// html table
echo "<table border='1'>";

// print out table columns
echo "<tr>";
foreach(array_keys($keys) as $key) {
 switch($sort) {
  case $key :
   $order_by = $key;
   break;
 }

 if($sort==$key) {
  echo "<td bgcolor=white><a href='?sort=$key&sort_by=" . $sort_order . "'>$key</a></td>";
 } else {
  echo "<td bgcolor=white><a href='?sort=$key&sort_by=" . $sort_order . "'>$key</a></td>";
 }
}

echo "</tr>";

// print out table contents
$query  = mysqli_query($cn,$sql);
while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
 echo "<tr>";
 foreach($row as $rows) {
  echo "<td>" . $rows . "</td>";
 }
 echo "</tr>";
}

echo "</table>";
  
echo '<br><br><br>';
    }
Posted
Updated 7-May-19 3:55am
Comments
Richard Deeming 9-May-19 12:04pm
   
YourPage.php?sort=%20pendreckey;DELETE%20FROM%20pin;--


Your code is vulnerable to SQL Injection[^].

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
PHP: SQL Injection - Manual[^]

Unfortunately, you can't specify the sort order in a parameter. Instead, you will need to verify that the $sort value is an exact match for a valid sort expression before appending it to your query.

You're also missing a space between ORDER BY and the sort column.

I am not well versed in PHP or MySql, but I would try defining $sort and $sort_order before you define $sql which depends on them.
   
So when I do that and click on header it kicks me out to the login screen and not just refreshes data in table.

Any ideas?
   
Comments
Richard MacCutchan 7-May-19 10:02am
   
Please do not post replies/questions as solutions. Use the "Have a Question or Comment" button below the message you are replying to.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900