Prevent SQL injection in WebSQL database? (How to handle quotes in data?)

Question

I'm currently importing an xml export of a mysql database into a websql database for use in an online mobile experience.

Everything works fine and dandy until there are double quotes in whatever string I am inserting. Normally, in PHP I would be using something like: mysql_real_escape_string while inserting.

Options I know I can try is to write regex and make functions for adding/removing slashes. There are lots of examples on google for this - but what i'm looking to see is if anyone else has encountered this, and possibly has a better solution.

Thanks for any help!

`string.replace("\"", "\\\"")` Will replace `"` by `\"`. It looks a bit odd but that's because the escape character needs to be escaped as well, funzies :P — Halcyon, Apr 13 '12 at 21:36
that actually does work ... it converts spaces as well, but other then that seems like a solid solution. I guess I was over thinking this. haha. Post as an answer and i'll mark it correct! ( the escape answer ) — Roi, Apr 13 '12 at 21:37
The primary issue is SQL injection; accidental or otherwise. I hope you find some good answers. The *best* solution is to *use placeholders*, if they are supported. — , Apr 13 '12 at 21:48
yeah sql injection --- and the xml that im importing has some HTML in it... causing it to error out once it reaches a quot =/ — Roi, Apr 13 '12 at 21:50

Oleg V. Volkov · Answer 1 · 2012-07-29T17:00:30.080

6

Forget about escaping. Do the right thing: use placeholders. In this case data will never ever be treated as anything but raw data string. In Web SQL this can be done with executeSql. See pre-processing section on explanation on how this works.

Example straight from intro of document:

db.readTransaction(function (t) {
  t.executeSql('SELECT title, author FROM docs WHERE id=?', [id], function (t, data) {
    report(data.rows[0].title, data.rows[0].author);
  });
});

No matter what is in id variable, this request will look for verbatim value, never being interpreted as part of command.

edited Jul 29 '12 at 17:00

answered Jul 29 '12 at 16:55

Oleg V. Volkov

21,719
4
44
68

1

Thanks for providing a solution based on the spec. My solution is a hack. – Paul Sweatte Jul 29 '12 at 20:06
Can it be done with INSERT INTO tablename (colnames) VALUES (?,?,?...) ? – Alon Dec 30 '13 at 07:42

score 0 · Answer 2 · answered Jul 29 '12 at 16:45

0

A simple workaround would be to use an AJAX request to send the XML string to PHP, which would return the string with quotes escaped.

answered Jul 29 '12 at 16:45

Paul Sweatte

24,148
7
127
265

2

Round-trip from client to arbitrary remote server on forming each query and equal server load multiplied by number of clients? Are you even serious? – Oleg V. Volkov Jul 29 '12 at 16:56
The question didn't clarify the scenario, so we had different assumptions. I assumed going from localhost(127.0.0.1) to server-side once to untaint one large XML file. I've done such an operation successfully exporting from Wordpress. – Paul Sweatte Jul 29 '12 at 20:04

Prevent SQL injection in WebSQL database? (How to handle quotes in data?)

2 Answers2

Linked