SQLSTATE[42000]: Syntax error or access violation [ PHP ]

Question

I build SQL query with a method and then return it and use it.

$query = $this->buildSearchQuery($searchParams);            
return $this->db->query($query);

Unfortunately this throw me an error:

SQLSTATE[42000]: Syntax error or access violation: 1064 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 ''SELECT * FROM candidates WHERE firstname = ? AND surname = ?','Dante', 'Hickman' at line 1

I was searching for it because this looks like SQL syntax fail of previous script which build query so I did simple thing I dump this $query before I used it.

Dump return this:

"'SELECT * FROM candidates WHERE firstname = ? AND surname = ?','Dante', 'Hickman'" (81)

Which is correctly, string with 81 chars. After this, I try to put this to original query instead of variabile and it looks like this:

return $this->db->query('SELECT * FROM candidates WHERE firstname = ? AND surname = ?','Dante', 'Hickman');

This secod script run correcty so it looks query is build correctly, but still error. I am missing something?

I hope for any advise which can help me solve this problem.

p.s. Syntax of that query is from nette framework but system should be the same.

EDIT: adding buildSearchQuery()

function buildSearchQuery($searchParams)
    {
        $column = "";
        $values = "";
        $col = "";
        $i=0;
        // Trim to make sure user doesn't enter space there
        if((trim($searchParams->firstname)))
        {
            $column .= "firstname,";
            $i++;
        }
        if((trim($searchParams->surname)))
        {
            $column .= "surname,";  
            $i++;
        }

        if((trim($searchParams->specialization)))
        {
            $column .= "specialization,";   
            $i++;
        }           
        if($searchParams->english !== NULL)
        {
            $column .= "english,";
            $i++;
        }           
        if($searchParams->german !== NULL)
        {
            $column .= "german,";
            $i++;
        }           
        if($searchParams->russian !== NULL)
        {
            $column .= "russian,";
            $i++;
        }           
        if($searchParams->french !== NULL)
        {
            $column .= "french,";
            $i++;
        }           
        if($searchParams->school !== NULL)
        {
            $column .= "school,";
            $i++;
        }

        if((trim($searchParams->registrationDate)))
        {
            $column .= "registrationDate";
            $i++;
        }
        if($i > 0)
        {
            // If number of columns is bigger then 0 (if user fill atleast one input)                   
            $columns = explode(",", $column);       
            // Create list of values for query (name of columns and values)
            foreach($columns as $c)
            {                                                   
                if (isset($searchParams->$c)) {     
                    $values .= "'".$searchParams->{$c}."', ";               
                    $col .= $c." = ? AND ";                 
                }                       
            }
            // Remove last "," and space
            $values = substr_replace($values, "", -2);          
            $col = substr_replace($col, "", -5);    
            $query = $col."',".$values;
            $query = "'SELECT * FROM candidates WHERE ".$query;         
            //$query = substr($query, 0, -1); //remove last char ( ' in this case)
            return $query;
        }
        else
        {
            $query = "SELECT * FROM candidates";
            return $query;
        }
    }

`surname = ?','Dante', 'Hickman');` that's invalid syntax. What are you trying to do here? if you're trying to check for multiples, you need additional `AND` or `OR`. Plus, you're not closing your query correctly. It stops right at `AND surname = ?'`< So just remove `,'Dante', 'Hickman'` and you will see magic happen. — Funk Forty Niner, Nov 20 '15 at 15:36
Right, this is the syntax of nette db->query, instead of writing complicate PDO this syntax make it a bit more simple with same effect, in general what it does it send query first (part between ' and ' ) and then replace question marks "?" with strings on left, one by one in order. As I said, second query will run and return results, so syntax is correct but i expect I have other problem there — Andurit, Nov 20 '15 at 15:40
you'll need to post more code and what's relevant to `$query = $this->buildSearchQuery($searchParams);`. I don't know what you're trying to build/search for here. — Funk Forty Niner, Nov 20 '15 at 15:41
well, I think this line `$query = "'SELECT * FROM candidates WHERE ".$query;` either has one `'` quote too many, or missing and would also need to be concatenated `$query .= "SELECT * FROM candidates WHERE ".$query;`. If that doesn't help or fix it, wait for someone else to pick up on your question. — Funk Forty Niner, Nov 20 '15 at 15:48
Yea I already check this as well, unfortunately this " at start and at the end are just display buy dump function. — Andurit, Nov 20 '15 at 15:52
The error is telling you the problem, as did the first comment. You're trying to pass an invalid query. Your dump shows you trying to pass this string to the query command. `query("'foo','bar','baz'")` is not the same as `query('foo', 'bar', 'baz')`. — miken32, Nov 20 '15 at 18:11
hey @miken32 that quote at start and at the end is from DUMP function, its not really there normaly — Andurit, Nov 20 '15 at 19:15
Look at the error MySQL gives you. That string is being passed to MySQL as a query. It's not a query. You say "I try to put this to original query instead of variabile" but they are not the same thing. — miken32, Nov 20 '15 at 19:16

Jan Tojnar · Accepted Answer · 2015-12-20T03:37:03.407

The comments above are correct, you are passing a string as the only argument, instead of multiple arguments query expects.

One possible solution is creating an array and calling the method with array items as arguments (e.g. using call_user_func_array). You can however do better.

Nette\Database is quite powerful and it can build the query for you. When you pass an associative array like ["column1" => "value1", "column2" => "value2"] as the only argument of where method, it will create corresponding WHERE column1 = 'value1' AND column2 = 'value2' clause. And of course it will securely escape the values to prevent SQL injection.

You can, therefore, simplify your code into something like following:

$columns = ["firstname", "surname", "specialization", "english", "german", "russian", "french", "school", "registrationDate"];
$conditions = [];
foreach ($columns as $c) {
    if (isset($searchParams->$c) && trim($searchParams->$c) !== "") {
        $conditions[$c] = $searchParams->{$c};
    }
}
return $this->db->table('candidates')->where($conditions);

No if–else statement is needed as when the array is empty, NDB correctly doesn’t append the WHERE clause.

SQLSTATE[42000]: Syntax error or access violation [ PHP ]

1 Answers1