CODEWITHSUNDEEP
mysqlsql-update
mike
mike

Reputation: 5

escaping special character in mysql statements while using UPDATE

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

Upvotes: 0

Views: 1823

Answers (2)

e4c5
e4c5

Reputation: 53734

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 your field is a char or varchar you should modify your query as follows:

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

Upvotes: 1

James Linden
James Linden

Reputation: 59

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().

Upvotes: 0

Related Questions