在字符串中使用单引号用于预准备语句

Question

I have a Like condition in a query I need to filter on first and last name in a users table. My variables are set up like this:

$firstname = Input::get('firstname');
$lastname = Input::get('lastname');
$myparameters = array();
$filterfirst = "'%" . $firstname . "%'";
$filterlast = "'%" . $lastname . "%'";

My sql query Is being set up like this (dynamically):

$sqlstring = 
                    "SELECT t1.id,t1.first_name, t1.last_name, t2.function "
                    . "FROM users t1 JOIN user_details t2 "
                    . "ON t1.id = t2.user_id";
            if($firstname && $lastname){
                $sqlstring .= " WHERE lower(first_name) LIKE lower(?)"
                        . " OR lower(last_name) LIKE lower(?)";
                $myparameters = [$filterfirst,$filterlast];
            }
            else{
                if($firstname){
                    $sqlstring .= " WHERE lower(first_name) LIKE lower(?)";
                    $myparameters = [$filterfirst];
                }
                if($lastname){
                    $sqlstring .= " WHERE lower(last_name) LIKE lower(?)";
                    $myparameters = [$filterlast];
                }
            }

My query is being executed using a Laravel prepared statement:

$resultset = DB::select($sqlstring,$myparameters);

I've logged the values of my important variables like this:

Log::info($sqlstring);
Log::info($myparameters);
Log::info('first name: ' . $filterfirst);
Log::info('last name: ' . $filterlast);

I'm seeing my logged values like this:

[2015-06-26 09:32:52] local.INFO: SELECT t1.id,t1.first_name, t1.last_name, t2.function FROM users t1 JOIN user_details t2 ON t1.id = t2.user_id WHERE lower(first_name) LIKE lower(?) OR lower(last_name) LIKE lower(?)  
[2015-06-26 09:32:52] local.INFO: array (
  0 => '\'%jer%\'',
  1 => '\'%can%\'',
)  
[2015-06-26 09:32:52] local.INFO: first name: '%jer%'  
[2015-06-26 09:32:52] local.INFO: last name: '%can%'  

So my values are correct '%jer%' and '%can%' which are the values I need to go into my query, but when I put them into my variables array they turn into '\'%jer%\'' and '\'%can%\'' So the sql command being executed isn't working correctly (returning nothing).

Does anyone have any idea how I can work around this? So I need the array to contain the string with a single quote without adding the extra single quotes or slashes so I just need '%whateverIgavein%'

EDIT: tried using a key value array and named variables in my sql statement but doesn't make any difference appearantly (as soon as it goes into the array it turns into '\'%whateverIgavein%\''

Answer 1

You are making the mistake of adding quotes to the content of your variable:

$firstname = "Joe";
$filterfirst = "'%" . $firstname . "%'";
var_dump($filterfirst) //string(7) "'%Joe%'" <-- notice the extra quotes

To include just the needed % to your string:

$filterfirst = "%{$firstname}%";

or

$filterfirst = "%" . $firstname . "%";

will both result in string(5) "%Joe%"