% creates syntax error in sql LIKE search for json_encode

Question

Ok so I normally search and search until I find a solution but I think this is the first task that's beyond me. Let's hope it's not a stupid error! Here's my first post...

The code below is a modified store locator script and it all works fine until I add a % sign into the LIKE part of the sql statement. When it is not there I can get exact search results from the LIKE statement so I know all the rest of the code is fine but as soon as I add the % sign I get the error message.

What's going wrong guys?!

<?php

header('Access-Control-Allow-Origin: *');

$con = mysqli_connect("$host","$user","$password","$db");
if (!$con) {
die('Could not connect: ' . mysqli_error($con));
}

mysqli_select_db($con,"sakila");

// Get parameters
$mlat = $_POST["lat"];
$mlng = $_POST["lng"];
$radius = $_POST["radius"];
$gamename = $_POST["gamename"];
$idGames2 = $_POST["idGames2"];
$matchtype2 = $_POST["matchtype2"]; 


// Search the rows in the markers table
//change 3959 to 6371 for distance in KM
$sql = sprintf("SELECT (ignore this there's lots of requested columns), 
lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) 
AS distance FROM games WHERE name LIKE '%lue' AND matchtype=$matchtype2 HAVING distance < '%s' ORDER BY distance LIMIT 0 , 20 ",
  mysqli_real_escape_string($con,$mlat),
  mysqli_real_escape_string($con,$mlng),
  mysqli_real_escape_string($con,$mlat),
  mysqli_real_escape_string($con,$radius));
  
$result = mysqli_query($con,$sql);
$rows = array();
while($r = mysqli_fetch_assoc($result)) {
    $rows[] = $r;
}
mysqli_close($con); 
    
echo json_encode($rows);
?>

The LIKE statement should pick up the name 'blue where I have written '%lue' because there is a matching search result for it but it throws up the error.

Hope this was clear and concise enough! Any help appreciated!!

score 0 · Answer 1 · edited Dec 12 '22 at 21:36

First of all, please urge to remove really NOW such a thing:

$matchtype2 = $_POST["matchtype2"];
sprintf("SELECT (ignore this there's lots of requested columns), lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM games WHERE name LIKE '%lue' AND matchtype=$matchtype2 HAVING distance < '%s' ORDER BY distance LIMIT 0 , 20 ",

Injecting direct variables from external sources (such as $_POST) is THE way opened to bring system down, also known as... SQL injections.

So.

You use sprintf there, probably to replace %s by your value. That may be a good idea, but that means you cannot use "%" as you want anymore. So, you may escape it, or don't use sprintf, as you wish.

I strongly recommand you to use parameters binding in prepared statements, will help you a lot both with parameters AND security.

For example, with a good DB class (PDO), we may do as:

$sql = "SELECT (ignore this there's lots of requested columns), lat, lng, ( 3959 * acos( cos( radians(:d1) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(:d2) ) + sin( radians(:d3) ) * sin( radians( lat ) ) ) ) AS distance FROM games WHERE name LIKE  AND matchtype=:matchType HAVING distance < :distance ORDER BY distance LIMIT 0 , 20 "
$db->query($sql, array(
    'd1' => $d1,
    'd2' => $d2,
    'd3' => $d3,
    'matchType' => $_REQUEST['matchType'],
    'distance' => $dist
));

EDIT : FYI, escaping % in sprintf is performed by doubling the % sign : %% See more at PHP doc (as always our best friend) : http://php.net/sprintf

I am aware I might not be doing it the safest way at the moment but as I'm learning how to code at the same time I'll tighten up security once it's all up and running - cheers! — Tricky, Mar 11 '16 at 12:30
Please consider also the first element (SQL injections). That's REALLY a big issue, in any case. EDIT : erm, okay. Just don't get trapped by the "later, never". — Lpu8er, Mar 11 '16 at 12:32

% creates syntax error in sql LIKE search for json_encode

1 Answers1