Using LIKE Wildcard is causing random characters

Question

I have a php script with a query like so:

$b = $a[0];  // 2115
$c = $a[1];  // 2197
$sql = "SELECT DISTINCT a.document_id 
        FROM archive_locations a 
        INNER JOIN archive_locations b 
           ON (a.document_id = b.document_id) 
           AND 
           (
              a.street_address LIKE '%$b%' 
              OR a.location_name LIKE '%$b%' 
           )
           AND 
           (
              b.street_address LIKE '%$c%' 
              OR b.location_name LIKE '%$c%' 
           )
           ORDER BY a.document_id;";

But when I echo this query, I get this:

SELECT DISTINCT a.document_id 
FROM archive_locations a 
INNER JOIN archive_locations b 
   ON (a.document_id = b.document_id) 
   AND 
   (
      a.street_address LIKE '%!15%' 
      OR a.location_name LIKE '%!15%' 
   )
   AND 
   (
      b.street_address LIKE '!97%' 
      OR b.location_name LIKE '!97%' 
   )
   ORDER BY a.document_id;

It seems the character % is causing the issue. I tried escaping it, but there must be some special escape or pattern needed that I am unaware of. I could use some help. Thanks!

Use parameters., Don't munge query strings. – Gordon Linoff Mar 18 '18 at 15:50 — Gordon Linoff, Mar 18 '18 at 15:50
@Gordon Linoff - example please. – Dean Mar 18 '18 at 15:56 — Dean, Mar 18 '18 at 15:56

score 1 · Answer 1 · answered Mar 18 '18 at 16:00

1

You can try like this way with curly braces e.g { and } before and after the variable name.

$sql = "SELECT DISTINCT a.document_id 
        FROM archive_locations a 
        INNER JOIN archive_locations b 
           ON (a.document_id = b.document_id) 
           AND 
           (
              a.street_address LIKE '%{$b}%' 
              OR a.location_name LIKE '%{$b}%' 
           )
           AND 
           (
              b.street_address LIKE '%{$c}%' 
              OR b.location_name LIKE '%{$c}%' 
           )
           ORDER BY a.document_id;";

When to use curly braces:

When you are defining a variable inside a string, PHP might mix up the variable with other characters if using simple syntax to define a variable and this will produce an error

See more: When to wrap curly braces around a variable

answered Mar 18 '18 at 16:00

A l w a y s S u n n y

36,497
8
60
103

I had already tried that and unfortunately it doesn't work. Same results. – Dean Mar 18 '18 at 16:09
If it doesn't work for you then you can try like this also `LIKE '%" . $a. "%'"` . Let me know is that worked or not? – A l w a y s S u n n y Mar 18 '18 at 16:14
I'm using postgresql – Dean Mar 18 '18 at 16:23
I know you are using postgresql but without php? – A l w a y s S u n n y Mar 18 '18 at 16:25
No, I'm using php 7. `pg_escape_string` doesn't work either – Dean Mar 18 '18 at 16:28

Using LIKE Wildcard is causing random characters

1 Answers1