escaping special character in mysql statements while using UPDATE...

Question

I am trying to update a field using

UPDATE table set field='some_variable' where id=1;

The problem here is that the some_variable comtains data with multiple special characters in it. so I am not able to use 'some_variable' or "some_variable" as it breaks and fails when it encounters the first same character(' or "). How can I overcome this?

Thanks. Mike

Answer 1

There are two solutions, the first is to use mysql_real_escape_string() the second is to use prepared statements. You have not mentioned what your programming language is but it's sure to support either prepared statements or real escape.

In addition to real escape, if you field is a char or varchar you should modify your query as follows:

UPDATE table set field='some_variable' where id=1;

Answer 2

Generally, you just need to escape the reserved characters -- see MySQL docs for specific reference. If you are directly executing the query (ie: in mysql shell), you'll have to escape manually. Most languages will supply a function to escape for you -- in PHP, for example, it's mysql_real_escape_string().