MySQL quotes, double quotes mess

Question

I'm trying to insert content into a MySQL and it looks like that:

table name is foo and the columns are:

     id INTEGER,
     name VARCHAR(50),
     content VARCHAR(255),
     url VARCHAR(255)

the problem arises when I'm trying to insert string which has quotes and double quotes inside it and escaping must be done since when inserting strings into MySQL you need to surround it in single quotes.

How do I escape the following text ?

'<div><a href='http://www.ynet.co.il/articles/0,7340,L-4391031,00.html'><img src='http://www.ynet.co.il/PicServer3/2013/06/11/4677612/AP0AMR108-Main-2013-05-25T16-54-20.070Z239775_a.jpg' alt='צילום: AP' title='צילום: AP' border='0' width='116' height='116'></a></div>בית המשפט העליון אישר בהחלטה תקדימית לחיילים ושוטרים להצביע בבחירות - לראשונה מאז שנות ה-70. האיסלאמיסטים זעמו על הפסיקה בעוד הליברלים ואנשי האופוזיציה בירכו: "יש מדינות שמעניקות לאנשי הצבא שלהן להצביע בבחירות ובהם דרום אפריקה, ברזיל, הודו, רוסיה, ארצות הברית, ואפילו ישראל"'

Have you tried as string like this: `"some content \"yes!\""` ? — Bart Friederichs, Dec 29 '13 at 16:01
You didn't tag any programming language, but if you would use prepared statements, there is no need for escaping. — Bart Friederichs, Dec 29 '13 at 16:01
possible duplicate of [Prevent SQL injection in WebSQL database? (How to handle quotes in data?)](http://stackoverflow.com/questions/10148599/prevent-sql-injection-in-websql-database-how-to-handle-quotes-in-data) — Barmar, Dec 29 '13 at 17:19

score 0 · Answer 1 · answered Dec 29 '13 at 16:01

0

You escape any string only for what's around it. So if you want to put double quotes around this one, you have to protect only the double quote inside.

answered Dec 29 '13 at 16:01

dkellner

8,726
2
49
47

I'm getting the content from somewhere I can't control if inside it will have quotes or double quotes, it can have both – Alon Dec 29 '13 at 16:02

score 0 · Answer 2 · answered Dec 29 '13 at 16:01

0

You can escape a quote using \ also known as the escape character. Have a look at this page that showcases the available escape sequences.

answered Dec 29 '13 at 16:01

Ivaylo Strandjev

69,226
18
123
176

Barmar · Accepted Answer · 2013-12-29T17:24:05.110

0

You can escape quotes with backslash:

insert into foo (id,name,content,url) values (0, 'test123', '<div id=\'test\'>ת"\א היא עיר מצוינת</div>', 'http://www.cnn.com')

You can also use the opposite kind of quotes:

insert into foo (id,name,content,url) values (0, 'test123', "someContent's", 'http://www.cnn.com')

Here's how to do it with WebSQL parameterized queries:

transaction.executeSQL('INSERT INTO foo (id, name, content, url) VALUES (?, ?, ?, ?)',
    [0, 'test123', '<div id=\'test\'>ת"\א היא עיר מצוינת</div>', 'http://www.cnn.com'],
    callbackfn);

edited Dec 29 '13 at 17:24

answered Dec 29 '13 at 16:01

Barmar

741,623
53
500
612

That won't solve my issue since 'someContent' can be
ת"א היא עיר מצוינת
– Alon Dec 29 '13 at 16:06
Escape all the quotes with backslash, what's the problem? – Barmar Dec 29 '13 at 16:28
thx for your answer. I've updated the string that raises me the problem. Can you assist me on that please ? – Alon Dec 29 '13 at 17:08
The answer is the same: put a backslash before every quote. – Barmar Dec 29 '13 at 17:10
Are you doing this from a programming language like PHP? If so, you should use parametrized queries, they will take care of this automatically. – Barmar Dec 29 '13 at 17:11
var content = content.replace(/"/g,'\\"').replace(/'/g,"\\'") – Alon Dec 29 '13 at 17:12
How are you doing queries using Javascript? Web SQL? – Barmar Dec 29 '13 at 17:14
I can't use parametrized queries since I'm using WebSQL (sqllite) and it doesnt support it – Alon Dec 29 '13 at 17:14
According to the question I just linked to above, WebSQL does support parametrized queries. – Barmar Dec 29 '13 at 17:20
thx, I will try it tomorrow and will let you know.. thx alot! – Alon Dec 29 '13 at 17:22

score 0 · Answer 4 · answered Dec 29 '13 at 16:02

0

try this :

insert into foo (id,name,content,url) values (0,'test123','someContent\'s','http:\/\/www.cnn.com')

answered Dec 29 '13 at 16:02

Waqar Ahmed

5,005
2
23
45

MySQL quotes, double quotes mess

4 Answers4

Linked