How to handle quote characters in PHP?


As you know, Firebird escapes the single-quote (apostrophe) character in strings with another quote character. For example to store string "can't" into database you would write a query like this:

INSERT INTO t1 VALUES ('can''t');

The same thing should be done with PHP when you supply values as strings (i.e. you build the entire statement as a string):

ibase_query('INSERT INTO t1 VALUES (\'can\'\'t\')');

or

ibase_query("INSERT INTO t1 VALUES ('can''t')");

If you have values in variables, you can escape them using str_replace:

$cant = "can't";
$cant = str_replace("'", "''", $cant);
ibase_query("INSERT INTO t1 VALUES('$cant')");

Of course, using strings to build queries is not a very good idea. You should use parametrized queries and then you wouldn't have to escape anything.

You probably knew all this, now onto the advanced stuff. When variables are supplied by the user using a form (via GET or POST) PHP might change them. For example, when user types:

can't

into a form field, you might get either of these in the variable

can't
can''t
can\'t

It depends on two variables in php.ini configuration file: magic_quotes_gpc and magic_quotes_sybase. GPC stands for Get, Post, Cookie. When magic_quotes_gpc is on, all single, double quote and backslash characters in GET, POST and Cookie variables get escaped with backslash. So, ' becomes \'.

When magic_quotes_gpc is off, variables are set as-is, the way user entered them. Magic quotes were supposed to help programmer as it is standard for MySQL, but actually brought a lot of confusion and default setting is off in newer versions of PHP.

The other setting is really useful to Firebird users: magic_quotes_sybase means to escape single-quote with another single-quote instead of backslash. Exactly the way Firebird does.

Main problem with magic quotes is that subsequent usage of variables (you take variable from one form and use in another form) adds additional levels of escapes, so if you see stuff like \\\\\\\\' you can surely tell it's happening because of some magic_quotes setting.

If your PHP application is going to be installed on various servers without you having control over php.ini, it's best to have a generic function like my_quotes() that would detect the magic_quotes settings and convert the string accordingly.


Do you find this FAQ incorrect or incomplete? Please e-mail us what needs to be changed. To ensure quality, each change is checked by our editors (and often tested on live Firebird databases), before it enters the main FAQ database. If you desire so, the changes will be credited to your name. To learn more, visit our add content page.



All contents are copyright © 2007-2024 FirebirdFAQ.org unless otherwise stated in the text.


Links   Firebird   News   FlameRobin   Powered by FB: Home Inventory   Euchre  
Add content   About  

Categories
 Newbies
 SQL
 Installation and setup
 Backup and restore
 Performance
 Security
 Connectivity and API
 HOWTOs
 Errors and error codes
 Miscellaneous