Unable to match results that contain apostrophe

Question

I have a db table that contains some business names.

I need to query by name and usually have no problems unless the name contains an apostrophe.

("
SELECT*
FROM t1
WHERE bus_name = '".$busName."' 
")

I've experimented and tried using static value, like this:

("
SELECT*
FROM t1
WHERE bus_name = \"Bob's store\" 
")

and it worked. Tried:

("
SELECT*
FROM t1
WHERE bus_name = \"".$busName."\" 
")

and it doesn't. What am I missing?

You have to escape the content of the argument, not the quote chars. So either do it manually by using one of the escape functions offered by your php mysql extension, or, preferred, learn about the benefits of "prepared statements" and "parameter binding". — arkascha, Nov 13 '15 at 16:20

score 1 · Answer 1 · answered Nov 13 '15 at 16:21

1

You should prepare the query, here is an example using PDO

$stmt = $pdo->prepare("
SELECT*
FROM t1
WHERE bus_name = :busName
");
$stmt->bindValue(':busName', $busName);
$stmt->execute();
var_dump($stmt->fetchAll());

answered Nov 13 '15 at 16:21

meda

45,103
14
92
122

Hmm, I already use a library to access the db: $bus = $db->get_row(" SELECT* FROM t1 WHERE bus_name = '".$busName."' "); How do I combine it with this PDO? – santa Nov 13 '15 at 16:32
Well your library needs to prepare the query, which extension does it use pdo or mysqli – meda Nov 13 '15 at 16:36
@santa - what library are you using? – andrewsi Nov 13 '15 at 16:39
Pretty old one, called ezsql – santa Nov 13 '15 at 16:40
I dont know this library, to escape the single quote do `$busName = str_replace("'", "''", $busName);` – meda Nov 13 '15 at 17:51

score 0 · Answer 2 · answered Nov 13 '15 at 16:27

Use prepared statements for this ( or any ) case.

$pdo = new PDO(...);
$stmt = $pdo->prepare('SELECT ... FROM ... WHERE foo = :foo');

$stmt->bindValue(':foo', "foo ' bar");
$stmt->execute();

Your source at its current state is vulnerable against Command Injections ( SQL Injection ).

Unable to match results that contain apostrophe

2 Answers2