Using wildcards in prepared statement - MySQLi

Question

I'm trying to run the following query, and I'm having trouble with the wildcard.

   function getStudents() {
        global $db;
        $users = array();
        $query = $db->prepare("SELECT id, adminRights FROM users WHERE classes LIKE ? && adminRights='student'");
        $query->bind_param('s', '%' . $this->className . '%');
        $query->execute();
        $query->bind_result($uid, $adminRights);
        while ($query->fetch()) {
            if (isset($adminRights[$this->className]) && $adminRights[$this->className] == 'student')
                $users[] = $uid;
        }
        $query->close();
        return $users;
    }

I'm getting an error that states: Cannot pass parameter 2 by reference. The reason I need to use the wildcard is because the column's data contains serialized arrays. I guess, if there's an easier way to handle this, what could I do?

Thanks in advance!

possible duplicate of [SQL like statement problems](http://stackoverflow.com/questions/618527/sql-like-statement-problems) — outis, Apr 27 '12 at 19:55

score 27 · Accepted Answer · answered Aug 29 '09 at 18:08

You have to pass parameters to bind_param() by reference, which means you have to pass a single variable (not a concatenated string). There's no reason you can't construct such a variable specifically to pass in, though:

$className = '%' . $this->className . '%';
$query->bind_param('s', $className);

score 14 · Answer 2 · edited Feb 13 '13 at 21:55

14

Another way to do this is:

SELECT id, adminRights FROM users 
  WHERE classes LIKE CONCAT("%", ?, "%") && adminRights='student'

This is handy in case you have a dynamic result bind and only want to change the SQL query...

edited Feb 13 '13 at 21:55

Andrew Barber

38,454
20
92
120

answered Feb 13 '13 at 21:53

Dominik

5,682
7
37
55

Insanely helpful. bind param was useless for me due to constantly shifting variable counts. – lilHar Feb 22 '20 at 23:49

score 6 · Answer 3 · answered Aug 29 '09 at 18:07

6

Parameter #2 must be a reference, not a value. Try

$param = '%' . $this->className . '%';
$query->bind_param('s', $param);

answered Aug 29 '09 at 18:07

VolkerK

93,904
19
160
225

Correct, as first sentence in manual says: "Binds variables to a prepared statement as parameters" (emphasis on variables) – Zed Aug 29 '09 at 18:10

score 1 · Answer 4 · answered Jun 06 '10 at 16:58

1

It is the same reason that happens in C++. When you pass a value to a function which expects the argument to be a reference, you need a variable ( not temporary ). So first create a variable and then pass it.

answered Jun 06 '10 at 16:58

siddhusingh

1,732
4
22
29

score -5 · Answer 5 · edited Feb 27 '14 at 23:36

-5

The existing answers didn't work for me so this is what I used instead:

 $sql = mysql_query("SELECT * FROM `products` WHERE `product_title` LIKE '$userInput%'") or die(mysq_error());

And it work all the time.

and just to top it all I just tried the simplest form and it worked

$sql = "SELECT * FROM `products` WHERE `product_title` LIKE '%".$userInput."%'";

I hope this helps

edited Feb 27 '14 at 23:36

Jeroen Vannevel

42,521
22
100
163

answered Nov 02 '11 at 20:00

mindmyweb

828
8
13

4

The second query is dangerous and should ***never*** be used as it is open to SQL injection attacks. – zzzzBov Feb 28 '14 at 02:07
4

Perfect example of what *not* to do. – Nate I Jun 10 '16 at 17:20

Using wildcards in prepared statement - MySQLi

5 Answers5

Linked

Related