dynamic string sent to db receives MySQL error near '%'

[Tarik Monem]

Hi Everyone,

I need your help.

I'm probably missing something simple but here's the string that I'm trying to send to the db:

 
Select distinct (email_address) from some_table where age BETWEEN 18 AND 19 AND state like 'ca%' 
 

If I copy and paste this statement directly into the SQL textarea of phpmyadmin it returns the result set that I am looking for with no problem.

However, when I try to send it via AJAX/Flex:

AJAX receives this line from Flex --

var mySqlStr = "Select distinct (email_address) from some_table where age BETWEEN " + age1.text + " AND " + age2.text + " AND state like '" + state.text + "%';";
ExternalInterface.call('sendRequestPost_search', mySqlStr);
 

Which sends this string to a php page. The php page catches the $_POST['mySqlStr'] and uses it to send the sql string to the database.

$con = mysql_connect($server, $username, $password);
 
    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }
    mysql_select_db("db", $con);
    $my_sql_string = $mySqlStr;
    if (!mysql_query($my_sql_string,$con))
      {
            die('Error: ' . mysql_error());
      }
    else
      {
     $myDetails = '<table><tr colspan=2><td valign=top>';
                        $someVar = mysql_query($my_sql_string,$con);
                        while($row = mysql_fetch_array($someVar))
                        {
                         $myDetails .= 'EMAIL_ADDRESS: '. $row["EMAIL_ADDRESS"];
                        }
        $myDetails .= '</td></tr></table>';
       }
       echo $myDetails;
 

Instead of a table of emails, I receive this error:

Error: 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 '\'ca%\'' at line 1
 

I've tried to write the string without the single quoutes. I've tried to add back slashes. I've tried it without the semicolon at the end. I've tried to send the string without the "%" altogether and insert it within the php page before sending the sql string off to the db.

What can I do?

Thanks in advance,
Tarik

[ronverdonk]

Try to remove the backslashes using [php]$my_sql_string = stripslashes($mySqlStr);[/php]Ronald

[mwasif]

Print the value of $my_sql_string, this will tell you what is the exact problem. It seems to be a problem with the parameter value you are passing for state.

You should only pass the parameters, validate them and use it in your SQL query. You can also use mysql_real_escape_string() to make your query safe.

[Tarik Monem]

Thanks for the feedback guys. I did echo the string and it was fine the way that it was (I tested the echoed string by copying & pasting it into phpmyadmin sql section) and I tried addslashes and htmlentities, but I don't know why I didn't think of stripslashes.

Anyways! This was the solution that I came up with in the mean time, was to split the string and bring it back together again in PHP.

Within Flex I broke up the string that was being passed to the AJAX(js) file, as such:

        public function myCallToDB():void
        {
            var o:Object = new Object();
            o.mySqlStr = "Select distinct (email_address) from some_table where age BETWEEN " + age1.text + " AND " + age2.text + " AND state like ";
            o.mySqlStr2 = state.text;
            o.mySqlStr3 = shows.text;
            ExternalInterface.call('sendRequestPost_search', o);  
        }
 

Within the AJAX(js) file:

// Set path to PHP script
  var phpscript = 'login.php';
 
    function sendRequestPost_search(o)
    {
        var mySqlStr = o.mySqlStr;
        var mySqlStr2 = o.mySqlStr2;
        var mySqlStr3 = o.mySqlStr3;
 
        // Open PHP script for requests
            http.open('post', phpscript);
        http.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
        http.onreadystatechange = handleResponsePost;
        http.send('mySqlStr='+mySqlStr+'&mySqlStr2='+ mySqlStr2+'&mySqlStr3='+ mySqlStr3);
    }
 

Within the PHP file the three strings are placed back together:

$mySqlStr = $_POST['mySqlStr'];
$mySqlStr2 = $_POST['mySqlStr2'];
$mySqlStr3 = $_POST['mySqlStr3']; 
 
    $con = mysql_connect($server, $username, $password);
 
    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }
    mysql_select_db("db", $con);
    $my_sql_string = $mySqlStr."'".$mySqlStr2."%'  AND shows like '".$mySqlStr3."%';";
 

The PHP files results is returned to the AJAX(js) file, which writes the results to a div called "response":

function handleResponsePost() 
{
    if(http.readyState == 1)
    {
        document.getElementById("response").innerHTML = "Please wait, loading... " ; 
    } 
    else if(http.readyState == 4 && http.status == 200)
    {
        // Text returned from PHP script
        var response = http.responseText;
        document.getElementById("response").innerHTML = response;
        if(response) 
        {
            // Update ajaxTest2 content
            document.getElementById("response").innerHTML = response;        
        }
    }
}
 

And it worked so well, that as you can see, there was room to add another param from the original sql string to further refine the select statement result set.

I bet the stripslashes would have done it :-)

And thanks for the help once again.

[ronverdonk]

We're all glad to have been of help. See you next time.

Ronald