SQL query not working when trying to use LIKE % expression

Question

I have never used the LIKE expression before, so perhaps I am missing something out of my coding.

I want to say, if any of the text matches what's in the status column in the database, output it.

        $searchText = $_GET["searchText"];
        $sql = "SELECT status FROM tbl_status WHERE status like '% $searchText %'";

        $q   = $conn->prepare($sql);
        $q->bindValue(':who',$who,PDO::PARAM_INT);
        $q->execute();

            while($r = $q->fetch(PDO::FETCH_ASSOC)){     
                    echo $r['status'];
                  }

Currently, no result is shown on the page, despite the fact the information is 100% in the database.

Updated code, due to errors. (Still outputting result)

$sql = "SELECT status FROM tbl_status WHERE status like '%:searchText%'";

        $q   = $conn->prepare($sql);
        $q->bindValue(':searchText',$searchText,PDO::PARAM_INT);
        $q->execute();

            while($r = $q->fetch(PDO::FETCH_ASSOC)){     
                    echo $r['status'];
                  }

What's this `$who` that you're binding? I can't see any `:who` in your query.... but if you're using bind variables, why not bind your searchText? — Mark Baker, Mar 17 '14 at 10:39
Your code is vulnerable to SQL injections. You should read on [how to prevent them in PHP](http://stackoverflow.com/q/60174/53114). Also note that there are spaces between `%` and the entered value. — Gumbo, Mar 17 '14 at 10:40
Ah yes, I have used the :who variable by mistake. I have updated the code, but it still does not work. I'll add my updated code. — Elaine Adams, Mar 17 '14 at 10:43
Don't use quotes in your SQL statement around bind variables; And add the '%' to $searchText, not to your statement — Mark Baker, Mar 17 '14 at 10:45

Ziv · Answer 1 · 2014-03-17T10:51:37.947

Remove the spaces around the $searchText variable.

And a comment about security: By using content directly from GET global, user can inject whatever he want into your query, and can change your data - So, you have to sanitize the input.

Read here about Sql Injection

About quoting a values for SQL statements with LIKE:

$name = 'myname';
$st   = $pdo->prepare("SELECT ... WHERE name LIKE :name");
$st->execute(array('name' => "%$name%"));

score 2 · Answer 2 · answered Mar 17 '14 at 10:38

2

try getting rid of the spaces around the variable

$sql = "SELECT status FROM tbl_status WHERE status like '%$searchText%'";

answered Mar 17 '14 at 10:38

mmeasor

459
3
19

score 0 · Accepted Answer · answered Mar 17 '14 at 10:43

0

You're using bind variables already, so bind your searchtext

$searchText = $_GET["searchText"];
$searchText = '%' . $searchText . '%';

$sql = "SELECT status FROM tbl_status WHERE status like :search";
$q = $conn->prepare($sql);
$q->bindValue(':search', $searchText, PDO::PARAM_STR);
$q->execute();

And note that your searchtext is a string, not an integer

answered Mar 17 '14 at 10:43

Mark Baker

209,507
32
346
385

Ah, perfecto! I was told by someone that my code is vulnreble to Injection. Is that true? - I'm PDO-ing, or so I thought – Elaine Adams Mar 17 '14 at 10:45
1

Your code was vulnerable, just using PDO isn't a magic panacea, especially as you were trying to inject the searchText value (user input) directly into your statement.... always use bind variables properly though, and it should protect against SQL injection – Mark Baker Mar 17 '14 at 10:47
Thanks, although that may have opened a can of worms for my entire site now when getting address bar variables! ALSO, i have just realised... I need any $searchText to start with a hash tag. #this #andthis #forexample – Elaine Adams Mar 17 '14 at 11:09
Just modify `$searchText = '%' . $searchText . '%';` to `$searchText = '%#' . $searchText . '%';` – Mark Baker Mar 17 '14 at 11:13

score 0 · Answer 4 · answered Mar 17 '14 at 10:55

Sanitize user input the best you can (make it what you actually expect !) and then use prepared statements...and you should be fine.

Queries like the one you wrote are not at all optimized and being so, are time consuming. That can be problematic when your table has a huge number of rows (full table scan). Make sure you can't avoid searching against '%$searchText%' and if by any chance you can, make sure you set up an index on the field you are searching against (that is, "status") to reduce the cost of such a search drastically.

SQL query not working when trying to use LIKE % expression

4 Answers4