SQL query on windows IIS server 2003 breaks with passed variable, jQuery AJAX

Question

Working on a current project I have encountered a strange issue. I have an html page that performs an AJAX call to a php file, passing a variable. The php file creates a SQL query using this variable, generates an XML file, and returns the result.

On my webserver this all works fine. However, when moved to a Windows 2003 IIS (permanent home) server it breaks.

Observations thus far... This breaks on windows server:

$qry  = "SELECT * FROM structure_name WHERE ID = $variable ORDER by ID ASC";
$results = mysql_query($qry);

This breaks on windows server:

$variable = 5; 
$qry  = "SELECT * FROM structure_name WHERE ID = $variable ORDER by ID ASC";
$results = mysql_query($qry);

This breaks on windows server:

$variable = 5; 
$qry  = "SELECT * FROM structure_name WHERE ID = " + $variable + "ORDER by ID ASC";
$results = mysql_query($qry);

This works:

$qry  = "SELECT * FROM structure_name WHERE ID = 5 ORDER by ID ASC";
$results = mysql_query($qry);

So, it appears the the server does not like a variable to be included in the query string. Any suggestions?

Answer 1

What error message are you seeing? If you aren't seeing the error (or you're just getting a HTTP 500 error), then turn on errors in your PHP.ini file or look in your PHP error log. That will give us some good insight into what's going wrong.

Your third statement does have two problems, though:

1. It needs a space before the word ORDER, otherwise $variable is smashed up against it like this: WHERE ID = 5ORDER by.
2. It is using plus signs, which is addition in PHP, not concatenation. Use dots/periods instead to concatenate it as a string.

It should look like this:

$variable = 5; 
$qry  = "SELECT * FROM structure_name WHERE ID = " . $variable . " ORDER by ID ASC";
$results = mysql_query($qry);

Another idea: use a MySQL profiler (http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html) to see what SQL statement is being sent to your MySQL server. It could be that your MySQL server is generating the error. You could also see if MySQL has an error log you could check

Also, you might want to escape the input to avoid SQL injection attacks. Try using this:

$variable = 5;
$qry = sprintf("SELECT * FROM structure_name WHERE ID = %d ORDER by ID ASC",
    mysql_real_escape_string($variable));
$results = mysql_query($qry);

For more information on mysql_query and sql injection attacks follow these links:

• http://en.wikipedia.org/wiki/SQL_injection
• http://php.net/manual/en/function.mysql-query.php