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.