Problem with quotes and paramterized SQL query

Question

I have a very simple search form, that takes in a term from GET_, and then incorporates this into an SQL query. I am trying to use this string from GET in a paramterized query, like so:

$searchString = '%' . $searchString . '%';
$recordsQuery = "SELECT username, firstname, lastname FROM $table WHERE lastname = $searchString" . $max;

  if ($getRecords = $con->prepare($recordsQuery)) {
        $getRecords->bind_param("s", $searchString);
        $getRecords->execute();
        $getRecords->bind_result($username, $firstname, $lastname);
        $rows = array();

        while ($getRecords->fetch()) {
            $row = array(
                'username' => $username,
                'firstname' => $firstname,
                'lastname' => $lastname,
            );
             $rows[] = $row;
        }
        return $rows;
    }

However, this results in the error

Unkown column 'term' in 'where clause'.

I assume this is becase my term is not quoted, however adding escaped quotes to the variable did nothing.

Any syntax errors or such are a product of modification to ask this question, and do not exist in my actaul code.

OK, I fixed this by changing the following lines:

 $searchstring = "'" . $searchstring . "'";
$recordsQuery = "SELECT username, firstname, lastname FROM $table WHERE lastname = $searchString" . $max;

I am sure this approach is probably bad because it is not paramterized..., but I was unable to get it working any other way.

Arkh · Accepted Answer · 2009-10-22 14:38:45Z

1

You forgot some quotes : $searchString = '"%' . $searchString . '%"';

But why do you build the request like that when you could use bound parameters : http://www.php.net/manual/fr/pdostatement.bindparam.php


$searchString = '%' . $searchString . '%';
$recordsQuery = "SELECT username, firstname, lastname FROM $table WHERE lastname = :lastname" . $max;
if ($getRecords = $con->prepare($recordsQuery)) {
        $getRecords->bind_param(":lastname", "%".$searchString."%");
        $getRecords->execute();
        $getRecords->bind_result($username, $firstname, $lastname);
        $rows = array();
        while ($getRecords->fetch()) {
            $row = array(
                'username' => $username,
                'firstname' => $firstname,
                'lastname' => $lastname,
            );
             $rows[] = $row;
        }
        return $rows;
    }

edited Oct 22, 2009 at 14:38

answered Oct 22, 2009 at 14:32

Arkh

8,44043 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Roger Sinks Over a year ago

Adding the quotes changed nothing.

Arkh Over a year ago

What is in the $max var exactly ?

Roger Sinks Over a year ago

Just an int, a caluulation done to see how many records to display.

Arkh Over a year ago

So, you have a request like "SELECT username, firstname, lastname FROM $table WHERE lastname = %term%42" ?

Roger Sinks Over a year ago

No, it should be like SELECT username, firstname, lastname FROM USERS WHERE lastname = 'term' LIMIT 0 , 10

|

Collectives™ on Stack Overflow

Problem with quotes and paramterized SQL query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related