1

I m creating page in which user enters commnets and that comments are inserted into DB(mysql). These comments can contain single,double quotes or any special chars. To escape these I used following code

 $str = mysql_real_escape_string($str,$conn);

here $conn is active connection resource, $str is string content from textarea

This works fine and return perfectly escaped string that I can insert into DB. But if user typed his/her comments into text editor like openoffice writer or msword and use this text from it, the error occur and gives error as follow while inserting in DB

Incorrect string value: '\x93testi...' for column 'commnets' at row 1

I think this is happening because single-double quotes in text that are coming from text editor(openoffice, msword) is not escaped properly. So How do I escape it to insert it into DB. Please help me

Thanks in advance.....

user392406
  • 1,323
  • 5
  • 28
  • 53
  • What character set is the database created with? I can remember having a similar problem when the database didn't allow 8 bit characters. – Joachim Isaksson Jan 10 '12 at 11:01
  • 3
    Important info you've omitted: encoding (UTF-8, Latin1, ASCII...), original string, escaped string and SQL query. `0x93` is the `“` character in Latin-1 and it doesn't have any special meaning in SQL syntax. – Álvaro González Jan 10 '12 at 11:03
  • character set for my mysql DB is utf8_unicode_ci(collation) – user392406 Jan 10 '12 at 12:49
  • Single quotes in string are escaped properly, the only problem is with double quotes that are not escaped and giving error. Is there any way to replace these double quotes with normal double quotes? – user392406 Jan 11 '12 at 05:36
  • The mysql extension is outdated and on its way to deprecation. New code should use mysqli or PDO, both of which have important advantages, such as support for prepared statements. Prepared statement parameters aren't vulnerable to injection, so you don't need to quote them. – outis Jan 14 '12 at 07:04

2 Answers2

0

There is one way to sidestep all this real_escape malarkey and inject INTO sql what is actually supplied, and that is to use mysql's ability to interpret a hexadecimal number of arbitrary length as a string.

e.g.

$query=sprintf("update module set code=0x%s where id='%d'", bin2hex($code), $id);

This works even if code is a BLOB type binary field and $code is full binary data (e.g, an image file contents).

You will also sidestep any sql injection with this. I have found that using sprintf to format queries is extremely powerful and safe and use of the php bin2hex() renders anything up to and including binary able to get into the database untainted.

Getting it out is somewhat another matter mind you..

Leo smith
  • 106
  • 4
0

You aren't submitting a valid UTF8 string to be saved in the DB. Instead it's probably a windows specific character set.

Presumably your users are submitting the text through a web page - you need to make sure that you serve the page in UTF8 and when the form is submitted it is also in UTf8 (which it will be by default if the page is served in UTF8).

You need to:

Make sure you're sending the UTF-8 charset in the headers.

header("Content-Type:text/html; charset=UTF-8");

And/or set the content type in your section of your page

btw mysql_real_escape_string is not really anything to do with the problem here. That function is used to prevent strings containing normal quotes from being used to do SQL injection attacks, which is better solved by using prepared statements anyway.

Danack
  • 24,939
  • 16
  • 90
  • 122