Page 1 of 1

Special characters in SQL strings

Posted: Sat Sep 03, 2011 2:33 am
by eazyGen
Hi guys,

I am viewing a tutorial that is a little out of date, but which does address the issue of stripping special characters from strings prior to using them in an SQL update statement.

What I would like to know is:

1. What is the very latest mechanism available to ensure your strings will be sent to the database with no special character problems?
2. Is the solution specific to MySQL, or does it hold good for other SQL databases?

Many thanks in advance,

S

Re: Special characters in SQL strings

Posted: Sat Sep 03, 2011 12:00 pm
by flying_circus
eazyGen wrote:Hi guys,

I am viewing a tutorial that is a little out of date, but which does address the issue of stripping special characters from strings prior to using them in an SQL update statement.

What I would like to know is:

1. What is the very latest mechanism available to ensure your strings will be sent to the database with no special character problems?
2. Is the solution specific to MySQL, or does it hold good for other SQL databases?

Many thanks in advance,

S

For MySQL specifically, you want to use the MySQLi extension rather than the older MySQL extension.

For escaping special characters in a mysql query, you have 2 options, you can use prepared statements (arguably the better way, but a little more work), or you can run your data through the real_escape_string function.

Those 2 solutions are mysql specific, but there are a couple of other database extensions that share similar escaping mechanisms, like PostgreSQL's pg_escape_string or pg_escape_bytea

Re: Special characters in SQL strings

Posted: Sat Sep 03, 2011 10:02 pm
by eazyGen
flying_circus wrote:
eazyGen wrote:Hi guys,

I am viewing a tutorial that is a little out of date, but which does address the issue of stripping special characters from strings prior to using them in an SQL update statement.

What I would like to know is:

1. What is the very latest mechanism available to ensure your strings will be sent to the database with no special character problems?
2. Is the solution specific to MySQL, or does it hold good for other SQL databases?

Many thanks in advance,

S

For MySQL specifically, you want to use the MySQLi extension rather than the older MySQL extension.

For escaping special characters in a mysql query, you have 2 options, you can use prepared statements (arguably the better way, but a little more work), or you can run your data through the real_escape_string function.

Those 2 solutions are mysql specific, but there are a couple of other database extensions that share similar escaping mechanisms, like PostgreSQL's pg_escape_string or pg_escape_bytea
Could you expand a little upon the highlighted item please?

Many thanks,

S

Re: Special characters in SQL strings

Posted: Sun Sep 04, 2011 1:24 am
by flying_circus
As I understand it, the major difference between real_escape_string and prepared statements is that real_escape_string will leave % and _ characters unescaped, which may or may not be desirable.

There is nothing I can say about prepared statements that the manual can't say better. Have a look here to get started: http://php.net/manual/en/mysqli.prepare.php

Re: Special characters in SQL strings

Posted: Sun Sep 04, 2011 5:13 am
by eazyGen
flying_circus wrote:For MySQL specifically, you want to use the MySQLi extension rather than the older MySQL extension.
Gracious. I had no idea this existed. Thanks very much for the intro to this.

S