ORDER BY DESC Limit Error

wonky1

Hi all,

I am using a php script to access a database that should display the users last comments in a table.
It used to work fine, but now I have updated phpmyadmin to avoid security holes - I get this;

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 'WHERE client_id = 2 ORDER BY msg_time DESC LIMIT 0, 50' at line 1

This is NOT my work, but I've edited it as much as I can, I have asked the dev's but I've asked twice since June with no reply and I'm hoping someone can see the problem.
I've got a feeling some of the text used is WAY out of date, if it IS the case I woull appreciate the help.

I'll remove some items from this script to protect their privacy for now;
It's too big for me to post all of it -

http://pastebin.com/K9tKMbcf

I have tried some online syntax checkers, but they threw wobblers within seconds.
I also tried following this thread without much success..

I would be very grateful for assistance, I've tried for a seriously long time to fix this.. I am not very good with sql/php et al and the developers aren't too fussed on answering me..
Cheers

bradgrafelman

wonky1;11020863 wrote:
I have updated phpmyadmin to avoid security holes

What else did you update? The only reason I ask is because phpMyAdmin has absolutely nothing to do with any SQL queries you execute in your PHP scripts.

Next, can you show us the code that's relevant to execute the SQL query that's failing? (As opposed to inundating us with 352 lines of "edited" code - even though we're probably only immediately interested in 2 or 3 of them.)

Before you do that, you might also do us (and yourself) another favor: echo out the SQL query string exactly as it was sent to MySQL. That way, we can examine the full query and get a better idea as to what the syntax error is.

wonky1

Thanks for replying,

I'm not entirely sure I follow but I'll do my best.
The only thing I updated is phpmyadmin, I had this problem before on an old setup too...(last year)
All I removed was usernames and sensitive details, the code itself was intact.

I'm not sure then, if it's a mysql error or phpmyadmin. I have had to edit their code before such as 'type=myisam to engine=myisam' to make it... work..

Here is the code I believe you mean;

<?php
include "ctracker.php";
error_reporting( E_ERROR ^ E_WARNING );

// Next line sets the echelon userlevel for this page. 1=superadmins - 2=admins - 3=moderators
$requiredlevel = 3;
require_once('Connections/b3connect.php');
require_once('login/inc_authorize.php');

$colname_rs_clientinfo = "1";
if (isset($_GET['id'])) {
  $colname_rs_clientinfo = (get_magic_quotes_gpc()) ? $_GET['id'] : addslashes($_GET['id']);
}
mysql_select_db($database_b3connect, $b3connect);
$query_rs_clientinfo = sprintf("SELECT * FROM clients WHERE id = %s", $colname_rs_clientinfo);
$rs_clientinfo = mysql_query($query_rs_clientinfo, $b3connect) or die(mysql_error());
$row_rs_clientinfo = mysql_fetch_assoc($rs_clientinfo);
$totalRows_rs_clientinfo = mysql_num_rows($rs_clientinfo);
$colname_rs_aliases = "0";
if (isset($_GET['id'])) {
  $colname_rs_aliases = (get_magic_quotes_gpc()) ? $_GET['id'] : addslashes($_GET['id']);
}
mysql_select_db($database_b3connect, $b3connect);
$query_rs_aliases = sprintf("SELECT * FROM aliases WHERE client_id = %s ORDER BY num_used DESC", $colname_rs_aliases);
$rs_aliases = mysql_query($query_rs_aliases, $b3connect) or die(mysql_error());
$row_rs_aliases = mysql_fetch_assoc($rs_aliases);
$totalRows_rs_aliases = mysql_num_rows($rs_aliases);
?>

Is this what you mean?
I believe this is the query, for this part of the program and databases.

I appreciate your help with this
Kind regards

Wonky

bradgrafelman

wonky1;11020881 wrote:
The only thing I updated is phpmyadmin

Then you must have at least changed something else. phpMyAdmin is just a GUI tool used to manage a MySQL database; it alone has absolutely no direct impact on the operation of the MySQL server or the interaction between it and any other application/scripts/etc. (unless, of course, you for some reason wrote an application that hijacked some of phpMyAdmin's files).

wonky1;11020881 wrote:
I'm not sure then, if it's a mysql error or phpmyadmin.

Hopefully it's clear now that it doesn't make sense for it to be the latter.

wonky1;11020881 wrote:
I have had to edit their code before such as 'type=myisam to engine=myisam' to make it... work

Sounds like their code is crap and shouldn't be used. 😉

wonky1;11020881 wrote:
Here is the code I believe you mean;

The failing query doesn't appear to be based on any part of the code you posted there. This should be obvious; the MySQL error message tells you that it realized you had a syntax error in the query when it encountered:

WHERE `client_id` = 2 ORDER BY `msg_time` DESC LIMIT 0, 50

Thus, it should follow that the code of interest should look like it will generate a SQL query that contains exactly that. I only see two MySQL queries being created in that code snippet, neither of which look similar to the part referenced in the error message.

Additionally, what I do see is some severely outdated code with some large security holes in it. For example, it: uses the 'mysql' PHP extension, depends on magic quotes, uses [man]addslashes/man to sanitize data for a SQL query, doesn't sanitize data appropriately (numeric data should be treated differently than string data, for example), uses the "or die()" method (e.g. pretty much the worst possible way you could handle an error condition other than not at all), ... I'll stop there.

wonky1

I'm not sure at all about what code should be or shouldn't be used as I'm not a coder or compiler of scripts. All I am trying to do is figure out why it's not working so I can try to fix it to get the results I need.

It's an ingame (fps) admin tool. This tool also has a web-based admin tool where you can manage players' behaviour and monitor any particular comments that should not be used ingame.

Most of it works well and the 'chat' log gathers data fine, however if I clicked on a players name ie -wonky, providing I've typed ingame, there should be a table in the web admin below my name displaying the most recent chats...

I've not done anything else either! It was a fresh install of MySQL phpmyadmin and so on. I then made my database names and imported the ones I needed. One of which is the chat logger SQL

It's a very small database that accesses the main core of the program to retrieve the data.

What's clear to you is not necessarily clear to others either. I may know some parts of an engine that you'd never heard of, therefore if I said crank shell - I would t expect you to immediately know what I'm talking about.

I am unsure what else I need to add. It throws this error and so far this was all I could find that related to user id and message time. Guess I need to look a bit further for where the call comes from?

Cheers for your help so far

wonky1

Ah I can't edit.

Clicking my name should show any recent chat, ( as long as the person has typed in game)
However it just shows this error message and not even a line from the table that should be there.
Sorry

Cheers

wonky1

Also, I am using mysql v. 5.0.10

And I added the code;

$startRow_rs_chats = $pageNum_rs_chats $maxRows_rs_chats;
$xlorderby_rs_chats = "id";
if (isset($GET['orderby'])) { ORDER BY
$xlorderby_rs_chats = (get_magic_quotes_gpc()) ? $GET['orderby'] : addslashes($GET['orderby']);
}
$xlorder_rs_chats = "DESC";
if (isset($GET['order'])) { DESC
$xlorder_rs_chats = (get_magic_quotes_gpc()) ? $GET['order'] : addslashes($GET['order']);
}
mysql_select_db($database_b3connect, $b3connect);
$query_rs_chats = sprintf("SELECT FROM chatlog ORDER BY %s %s", $xlorderby_rs_chats,$xlorder_rs_chats); ORDER BY
$query_limit_rs_chats = sprintf("%s LIMIT %d, %d", $query_rs_chats, $startRow_rs_chats, $maxRows_rs_chats);

as this is a typical error when viewing the log;
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 'WHERE client_id = 438 ORDER BY msg_time DESC LIMIT 0, 50' at line 1

Is there something else then, that I should be looking for?
I've asked the dev's what is calling this function and am waiting for replies.

In plain English, how out of date is this code?

wonky1

I managed to fix it.

Cheers..