0

I am trying to escape a string before inserting it to my DB, this string contains some characters that need to be escaped. For example :

 $mystring = "So, This is the string it's causing me headaches."

I want to insert this string into a field which has certain limit, say 50. But the problem is when I use the mysql_real_escape_string function it adds the \ to escape my single quote ' which makes my strings length 51 and therefore I could not insert it into the DB .

Irfan DANISH
  • 8,349
  • 12
  • 42
  • 67
user2613707
  • 79
  • 8
  • 1
    You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). – Quentin Nov 18 '13 at 11:40
  • 2
    The escaping is just for correct SQL literal format. The string `'foo\'bar'` has 7 characters, not 8. – deceze Nov 18 '13 at 11:42
  • @deceze That is not true, try it when you have a new line in your string then you will see what i mean. – user2613707 Nov 18 '13 at 13:48
  • So how exactly did the answer you accepted solve your problem?! – deceze Nov 18 '13 at 14:33

3 Answers3

-1

The backslash is not part of the string. It's just part of the string literal in the SQL statement. Think about it like this:

CONCAT('a', 'b', 'c')

That's quite a lot of characters in the SQL statement, but the resulting string of this statement is just abc with 3 characters. In the same way, this:

'a\'c'

is the correctly formatted literal for the string a'c with three characters.

If your string is actually getting longer after escaping, you're probably escaping too often and the \ becomes part of your actual string you're trying to store.

See http://sqlfiddle.com/#!2/76008/1 for a demo. 'a\'c' perfectly fits into a 3 char field.

deceze
  • 510,633
  • 85
  • 743
  • 889
  • Hi, the link to the demo is not working. Try to escape a string with a newline in it and you will get my problem. – user2613707 Nov 18 '13 at 13:55
  • The demo is working again now. And even with a line break there's no problem! `INSERT INTO foo (bar) VALUES ('a\nb')` inserts the *three character string* "ab". That's only three actual characters. – deceze Nov 19 '13 at 09:34
-1

Use mysqli_real_escape_string($mystring)

R R
  • 2,999
  • 2
  • 24
  • 42
Vijay Verma
  • 3,660
  • 2
  • 19
  • 27
-1

you can also use str_replace for the same,it always worked for me.

$mystring=str_replace("\'","'","$mystring");
$mystring=str_replace("'","\'","$mystring");
R R
  • 2,999
  • 2
  • 24
  • 42
  • What is this supposed to do?! You're replacing all `\'` by `'` and then again by `\'`?! – deceze Nov 18 '13 at 11:53
  • i used to do that for accidental insertion of '\',but i think one will work too.edited – R R Nov 18 '13 at 11:54
  • So you're *unescaping* the string again? Whatever you think you're doing here, it's the entirely wrong approach for this problem. – deceze Nov 18 '13 at 11:55
  • @deceze i am having trouble understanding whatever you are saying but i am sure this is what the user asked who posted the question. – R R Nov 18 '13 at 11:57
  • Again, please tell us **what this does.** When would you use it and *why*? Don't just throw code snippets out here the OP is supposed to use without explaining what it does. – deceze Nov 18 '13 at 12:04