SQL syntax error line 1 AJAX PHP & SQL

Question

I'm having issues with my SQL syntax according to the error message I'm getting, but can't figure it out. I'm trying to create a system where I can search a project name for matches using AJAX, PHP and SQL. Each match to the seach term is displayed on screen as a hyperlink which when clicked I intend to take me to another page that will then display any content thats relivant to the search term.

I've got it set up to search the database and display the hyperlinks but when I click on the hyperlink to load the relivant matches I get the error 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 'Home From Home''' at line 1"

Now inside the code you won't find Home From Home referanced anywhere becuase thats the search term that "search_value" picks up. I think my issue is with my query but I'm not 100% sure. I'll post the code from all 3 pages below so you can see how all the pages links together etc.

Where am I going wrong?

CPanel.php

<html>
    <head>
    <script>
        function showHint(str) {
            if (str.length==0) { 
                document.getElementById("results_box").innerHTML="Matching Project Names: <hr/>";
                return;
            }

            if (window.XMLHttpRequest) {
                // code for IE7+, Firefox, Chrome, Opera, Safari
                xmlhttp=new XMLHttpRequest();
            } else {
                // code for IE6, IE5
                xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
            }

            xmlhttp.onreadystatechange=function() {
                if (xmlhttp.readyState==4 && xmlhttp.status==200) {
                     document.getElementById("results_box").innerHTML=xmlhttp.responseText;
                 }
            }

            xmlhttp.open("GET","CPanel_Inc.php?search_value="+str,true);
            xmlhttp.send();
        }
    </script>
    </head>

    <body>
        <p><b>Project Search:</b></p>
         <form> 
            Project Name: <input type="text" onKeyUp="showHint(this.value)" size="20" />
         </form>
         <div id="results_box">
            Matching Project Names: <hr/>
         </div> 
    </body>
</html>

CPanel_Inc.php

<?php
    $connect = mysqli_connect("localhost", "root", "", "creative wolf");
    if (mysqli_connect_errno()) {
        printf("Connect Failed", mysqli_connect_error());
        exit();
    } else { 

        // Get the search_value parameter from URL
        $search_value = $_GET["search_value"];
        $content = "";

        // Search database for comparable values provided that search_value has some value
        if (strlen($search_value) > 0) {        

            // Sets up query return results
            $qry = "SELECT ProjectName FROM projects WHERE ProjectName LIKE '".mysql_real_escape_string($search_value)."%'";
            $res = mysqli_query($connect, $qry) or die(mysqli_error($connect));


            if (mysqli_num_rows($res) < 1){
                printf("Matching Project Names: <hr/> Could not retrieve records matching your search critera", mysqli_error($connect));
            } else {    
                $content .= "Matching Project Names: <hr/>";    
                while ($data = mysqli_fetch_array($res)) {
                    $content .= "<li><a href=\"search.php?search_value='". $data['ProjectName']."'\"> " . $data['ProjectName']. "</a></li>";
                }   
                echo "$content";
            }   
        }

        mysqli_free_result($res);
        mysqli_close($connect);
    }
?>

Search.php

<?php
    $connect = mysqli_connect("localhost", "root", "", "creative wolf");
    if (mysqli_connect_errno()) {
        printf("Connect Failed", mysqli_connect_error());
        exit();
    } else { 

        // Get the search_value parameter from URL
        $content = "";

        // Set up query
        $get_data_qry = "SELECT * FROM content WHERE ProjectName = '".$_GET["search_value"]."'";
        $get_data_res = mysqli_query($connect, $get_data_qry) or die(mysqli_error($connect));

        while ($clientData = mysqli_fetch_array($get_data_res)) {
            $content .= $clientdata['ProjectName'];
            $content .= $clientdata['RequestedBy'];
            $content .= $clientdata['FileLocation'];
            $content .= $clientdata['Description'];
            $content .= "<br />";
        }

        mysqli_free_result($res);
        mysqli_close($connect);
    }
?>

Also concatenating a $_GET variable into a SQL query is SQL Injection 101. Don't do it. Parameterized queries FTW. — digitlworld, Oct 23 '12 at 17:09

score 1 · Answer 1 · answered Oct 23 '12 at 17:08

You should print what $get_data_qry is.

If your .$_GET["search_value"]. has a single quote in it, your query will look like this:

SELECT * FROM content WHERE ProjectName = 'anything with a ' should be escaped ';

and this will cause a syntax error.

So you have to do like you did in CPanel_Inc.Php

"SELECT * FROM content WHERE ProjectName = '".mysql_real_escape_string($_GET["search_value"])."'"

score 0 · Answer 2 · answered Oct 23 '12 at 17:08

Try this in the search.php file:

"SELECT * FROM content WHERE ProjectName = '".mysql_real_escape_string($_GET["search_value"])."'"

Also check to make sure you are not appending any extra quotes or double quotes to $_GET["search_value"]. By the mysql error message, it appears there may be some extra quotes:

"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 'Home From Home ** ' ' ** ' at line 1"

score 0 · Answer 3 · answered Oct 23 '12 at 17:14

If it's after you click the link generated by:

$content .= "<li><a href=\"search.php?search_value='". $data['ProjectName']."'\"> " . $data['ProjectName']. "</a></li>";

I can see a problem. You're adding single quotes to the GET parameter search_value then passing the into the query:

"SELECT * FROM content WHERE ProjectName = '".$_GET["search_value"]."'"

The end result being:

SELECT * FROM content WHERE ProjectName = ''GET Value''

And those extra single quotes are probably at fault.

When you have trouble with queries like this, echoing the final query string is often good for debugging purposes.

For doing AJAX requests, I suggest not handling it by hand (which you appear to be doing) but use something like jQuery instead. And never, ever, ever, ever concatenate user input into a SQL statement. Ever. This opens the door for simple SQL injection methods that leave your database open for infiltration or damage.

score 0 · Answer 4 · edited May 23 '17 at 12:05

I know this is an old thread, but I wanted to add my 2cents on prepared statements in PHP. See here http://software-security.sans.org/developer-how-to/fix-sql-injection-in-php-using-prepared-statements and How can I prevent SQL injection in PHP? The PHP manual on prepared statements: http://php.net/manual/en/mysqli-stmt.prepare.php

Using a prepared statement as a default for pdo/mysql database operations will make your queries more structured and secure.

Also try to store your database configuration (user, pass, host) separately in a non-publicly accessible file (see How to secure database passwords in PHP?).

score -1 · Answer 5 · answered Oct 23 '12 at 17:55

Having read all the comments left and help offered I've found the soloution :).

As several of you have said, it was indeed an issue with my quotations. As Digitlworld suggested I echo'd out the output of my query and found that the value being passed to get value was the issue.

Having figured this out I've corrected the offending line of code, in CPanel_Inc.php, to read as follows

$content .= "<li><a href=\"search.php?search_value=". $data['ProjectName']."\"> " . $data['ProjectName']. "</a></li>";

I've also took on board what people were saying about SQL injection and have adapted my query to read as follows

$get_data_qry = "SELECT * FROM content WHERE ProjectName = '".mysql_real_escape_string($_GET["search_value"])."'";

Is there any good, but simply tutorials or guides that can help me combat SQL injection, I'm fairly new to this kind of project so any help will only help improve my abilities.

Thanks so much to everyone for helping, the combination of all your inputs helped me to find the soloution.

SQL syntax error line 1 AJAX PHP & SQL

5 Answers5