Using PDO query, without prepared statements, with multiple LIKE statements from multiple HTML input fields

Question

I have successfully gotten queries to execute and print in PDO, but I'm doing something wrong here. The important part of the code for this question is in the last couple blocks of code; I'm including the first portion just for clarity.

This code connects to an HTML form with multiple input fields. The PHP constructs a query by appending the data from each field with ANDs in the WHERE statement.

This is what throws me: I echo the $query variable, and I can see that the query is formed properly, but when I then try to print the query results, no results are printed.

I wrestled with using prepared statements here, and decided to try getting the code to work first without them after failing to construct a prepared statement with varying numbers of parameters. I did try, with the help of this post: LIKE query using multiple keywords from search field using PDO prepared statement

So, setting aside prepared statements for the moment, can anyone tell me what I'm doing wrong here? Any help would be greatly appreciated.

<?php
if(isset($_POST['submit'])) {

// define the list of fields
$fields = array('titleSearch', 'keywordSearch', 'fullSearch', 'fromYear', 'toYear', 
    'fromSeconds', 'toSeconds', 'withSound', 'withColor');
$conditions = array();

// loop through the defined fields
foreach($fields as $field){
    // if the field is set and not empty
    if(isset($_POST[$field]) && $_POST[$field] != '') {
        // create a new condition, using a prepared statement
        $conditions[] = "$field LIKE CONCAT ('%', $_POST[$field], '%')";
    }
}

// build the query
$query = "SELECT keyframeurl, videoid, title, creationyear, sound, color, 
     duration, genre FROM openvideo ";

// if there are conditions defined, append them to the query
if(count($conditions) > 0) {
    $query .= "WHERE " . implode(' AND ', $conditions);
}

//confirm that query formed correctly
echo $query; 

//print query results
foreach ($dbh->query($query) as $row){
   print $row['videoid'].' - '.$row['title'].'<br />';
}
}
?>

http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php — Jorge Y. C. Rodriguez, Apr 09 '13 at 19:15

score 1 · Answer 1 · edited May 23 '17 at 12:30

Instead of posting your query you have to run it.
That's the only way to fix the problem

a Stack Overflow passer-by do not have a database server in their head to run your query.
a Stack Overflow passer-by do not have your particular database server in their head to run your query.

So, you are the only one who can run your query against your database and ask it what's going wrong.

Turn on error reporting. Make sure sure you can see errors occurred. Try to add intentional error and see if it works.
Double-check your database data if it really contains desired values.
Double-check your input data, if it really match database values.
Run your assembled query against database in console or phpadmin.

Dig to some certain problem. Do not just sit and wait. Asking a question "I have a code it doesnt work" makes very little sense. Code have to be run, not stared into.

Thanks for the advice on checking for errors. I'm a beginner and haven't developed a good toolkit for debugging, so this was very helpful. — allenbell_nc, Apr 10 '13 at 00:20

score 0 · Answer 2 · answered Apr 09 '13 at 18:56

0

$conditions[] = "$field LIKE CONCAT ('%', $_POST[$field], '%')";

is the culprit: sending "something" for the title ends up in something like

WHERE titleSearch LIKE CONCAT('%', something, '%')

but you want

WHERE titleSearch LIKE CONCAT('%', 'something', '%')

with more quotes.

Be sure not to roll this out into production though, as you might end up with somebody posting "xxx') OR 1=1; --" just for the perofrmance fun, or even worse, depedning on their mood.

answered Apr 09 '13 at 18:56

Eugen Rieck

64,175
10
70
92

Thanks! I added the single quotes, though the code still isn't executing the query, so there must be some additional problem. And don't worry, I do intend to ultimately use prepared statements, I just wanted to get a simpler version of the code working first. Here's an example of a query printed by this code: SELECT keyframeurl, videoid, title, creationyear, sound, color, duration, genre FROM openvideo WHERE titleSearch LIKE CONCAT ('%', 'future', '%') AND withSound LIKE CONCAT ('%', 'withSound', '%') – allenbell_nc Apr 09 '13 at 19:39

score -1 · Accepted Answer · answered Apr 09 '13 at 18:54

-1

You've forgotten quotes around the $_POST values that you're directly inserting into your queries:

$conditions[] = "$field LIKE CONCAT ('%', '$_POST[$field]', '%')";
                                          ^--            ^--

so while this will fix your immediate problem, you'll still be wide open to sql injection attacks.

answered Apr 09 '13 at 18:54

Marc B

356,200
43
426
500

Thanks! That was definitely a problem that needed fixing, though my code still isn't executing the query after adding the single quotes. And to clarify, I intend to ultimately use prepared statements, I just was having trouble with them and wanted to try to get a simpler version to work. This is an example of the query that gets printed after adding the single quotes to '$_POST[$field]': SELECT keyframeurl, videoid, title, creationyear, sound, color, duration, genre FROM openvideo WHERE titleSearch LIKE CONCAT ('%', 'future', '%') AND withSound LIKE CONCAT ('%', 'withSound', '%') – allenbell_nc Apr 09 '13 at 19:36
then put in some proper error handling. e.g `$res = $db->query($sql) or die($db->errorInfo());`. – Marc B Apr 09 '13 at 19:54
I'm not seeing it, there are quotation marks around those variables. That statement starts with `"$field........ $_POST[$field]...."`, but maybe I'm missing something. I'm rather new to PHP. – lmat - Reinstate Monica Dec 18 '22 at 15:09

score -1 · Answer 4 · answered Apr 09 '13 at 20:11

You don't even need the CONCAT built-in function, you can model the whole string as $conditions[] = "{$field} LIKE '%{$_POST[$field]}%'". But you should use prepared statements if you don't want to face serious SQL injection attacks in the short-term future.

Why don't you try something like this? (using PDO as an example):

if ($pdo = new \PDO("mysql:host=localhost;dbname=testdb;charset=utf8", "user", "password")) {
    $fields = ["titleSearch","keywordSearch","fullSearch","fromYear","toYear","fromSeconds","toSeconds","withSound","withColor"];
    $parameters = array_map(function ($input) { return filter_var($input, FILTER_SANITIZE_STRING); }, $fields)
    $conditions = array_map(function ($input) { return (!empty($_POST[$input]) ? "{$input} LIKE ?" : null); }, $fields);
    $query = "SELECT `keyframeurl`,`videoid`,`title`,`creationyear`,`sound`,`color`,`duration`,`genre` FROM `openvideo`" . (sizeof($conditions) > 0 ? " " . implode(" AND ", $conditions) : null);

    if ($statement = $pdo->prepare($query, [\PDO::ATTR_CURSOR => \PDO::CURSOR_FWDONLY])) {
        if ($statement->execute((!empty($parameters) ? $parameters : null))) {
            $result = $statement->fetchAll(\PDO::FETCH_ASSOC);
        }
    }
}

Haven't tested it (just coming to my mind right now), but it should set up PDO, prepare a statement based on the conditions you seem to look for, add the parameters in the execute() method (pre-filtered, although there's FAR better filtering techniques) and return all results associated with your query.

Even if you decide not to use this, give it a thought at least... it's a good starting point on PDO and, of course, get a nice tutorial on GET/POST variable filtering (or use a 3rd-party tool like HTML Purifier, for that matter).

Hope that helps ;)

Using PDO query, without prepared statements, with multiple LIKE statements from multiple HTML input fields

4 Answers4