10.9. Escaping Quotes
Problem
You need to make text or binary data safe for queries.
Solution
Write all your queries with placeholders and pass values to fill the placeholders in an array:
$sth = $dbh->query('UPDATE zodiac SET planet = ? WHERE id = 2', array('Melmac')); $rows = $dbh->getAll('SELECT * FROM zodiac WHERE planet LIKE ?', array('M%'));
You can also use PEAR
DB’s DB::quote( )
to escape special characters and make sure
strings are appropriately marked (usually
with single quotes around them):
$planet = $dbh->quote($planet); $dbh->query("UPDATE zodiac SET planet = $planet WHERE id = 2");
If $planet
is Melmac
,
$dbh->quote($planet)
if you are using MySQL
returns 'Melmac'
. If $planet
is
Ork's Moon
,
$dbh->quote($planet)
returns 'Ork\'s Moon
‘.
Discussion
The DB::quote( )
method makes sure that text or binary data is appropriately quoted,
but you also need to quote the
SQL wildcard characters
%
and _
to ensure that
SELECT
statements return the right results. If
$planet
is set to Melm%
, this
query returns rows with planet
set to
Melmac
, Melmacko
,
Melmacedonia
, or anything else beginning with
Melm
:
$planet = $dbh->quote($planet); $dbh->query("SELECT * FROM zodiac WHERE planet LIKE $planet");
Because %
is the SQL
wildcard meaning “match any number of
characters” (like *
in shell
globbing) and _
is the SQL wildcard meaning
“match one character” (like
?
in shell globbing), those need to be
backslash-escaped as well.
Use strtr( )
to escape
them:
$planet = $dbh->quote($planet); ...
Get PHP Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.