SQLite Forum

The characters to be escaped in Sqlite3 to prevent Sql Injection
Login

The characters to be escaped in Sqlite3 to prevent Sql Injection

(1) By Ahmed Ramadan (ahmed.ramadan02) on 2021-09-04 08:54:12 [link]

I am working on a project that using sqlite and we are trying to escape the special characters which might lead to the Sql injections.

I made a research and I found that the escape schema is different per the database engine (e.g. sqlite is different from MySql). And I found out that in case of sqlite we need to escape only a ' (single quote) by just adding another single quote. but I have tried also to use " instead of ' and seems that the " should be escaped by adding another ".

=> So could anyone confirm my previous information? also please highlight if other characters need to be escaped?

(2) By Tim Streater (Clothears) on 2021-09-04 09:55:04 in reply to 1 [link]

You can avoid all this by using prepared statements.

(3) By Simon Slavin (slavin) on 2021-09-04 14:51:02 in reply to 1 [link]

Tim's reply is the key one here.  Prepared statements have no dangerous characters, including 0x00.  But to your point, the apostrophe character is the only character you need to worry about, and you've already found what to do about it.

You might want to escape other characters including the percent sign if you're using user-sourced strings for searching, but that's a detail of how your program works, and a dangerous thing to do anyway.  If you want to do it I suggest you read about the ESCAPE clause, as described in

<https://sqlite.org/lang_expr.html#the_like_glob_regexp_and_match_operators>

(4) By Keith Medcalf (kmedcalf) on 2021-09-04 17:14:23 in reply to 1 [link]

Escaping characters in SQL statements will not prevent so-called SQL Injection attacks.  

They (what those of limited intellect call SQL Injection) are a common class of problem where the unwashed confuse CODE and DATA and attempt to execute DATA.  It is extremely common in anything that Microsoft has touched, designed, or has anything whatsoever to do with.  Everything from Microsoft must be examined carefully for this common defect because Micrsofties Live to Destroy absolutely everything, and everything Microsofties touch is ill-conceived.

Keep your CODE separate from your DATA.  Stop executing DATA.

There is absolutely no need whatsoever for any type of quoting or escaping to avoid executing DATA.  Use one of the mechnanisms, most invented more than a century ago, to keep your CODE and DATA separate, and do not execute DATA.

There is actually nothing more simple in the multiverse.

(5) By anonymous on 2021-09-04 22:43:27 in reply to 1

Use prepared statements if possible. If that won't work, simply double each apostrophe in a string when including user data into a SQL code. (Quotations marks with `"` are in SQL only for names. You can double the quotation mark too for this purpose if needed.) When including numbers you can just use them as it is (if you have verified that it is in fact a number, and not some other type instead), and blobs can be written as hex with `X'...'`.

(6.1) By Warren Young (wyoung) on 2021-09-04 23:05:39 edited from 6.0 in reply to 5 [link]

…and if your attacker inserts double-quotes in his injected string, some escaped, some not?

No: use prepared statements, period, end of sentence. Do not try to play games, attempting to outthink your attacker. The many attackers collectively have all the time in the world, they need to succeed only once, and they are better-motivated than you are. You are alone and busy, you must succeed in *every* encounter with the attackers, and you have better things to do with your time. Use the simple solution that always works, so you can move on and do something productive with your day.

(7) By Scott Robison (casaderobison) on 2021-09-04 23:25:47 in reply to 6.1 [link]

Excellently put.

(8) By J.M. Aranda (JMAranda) on 2021-09-05 13:38:24 in reply to 7 [link]

Yes, a real hole in one.

(9) By ddevienne on 2021-09-06 06:48:47 in reply to 6.1 [link]

Well, yes. Except... If that was 100% true, then SQLite's `printf` wouldn't  
have `%q`, `%Q`, and `%w` as extensions, and [list them as advantages in #3](https://www.sqlite.org/printf.html#advantages).

So of course prepared statements and binding is better, but in a pinch,  
proper use of `sqlite3_mprintf()` will do the job.

It's not by chance they are in SQLite after all.

(10) By J.M. Aranda (JMAranda) on 2021-09-06 12:15:05 in reply to 6.1 [link]

I once collaborated with an African NGO. They send them old computers. You know, for schools, hospitals, etc. But what they really liked was.. spending days trying to decipher any possible residual content on the hard drive!

(11) By ddevienne on 2021-09-06 13:58:01 in reply to 10 [link]

And how is that relevant?

No offense, but many of your posts are off-topic and cryptic at best.  
I'm even surprised they pass moderation in fact. Could you please post  
only about technical content relevant to SQLite? And the OP's question?

(12) By Scott Robison (casaderobison) on 2021-09-06 15:21:18 in reply to 11 [link]

It is possible that someone is experimenting with Markov chains...

(13) By ddevienne on 2021-09-06 15:46:54 in reply to 12 [link]

I didn't realize it was a *live* turing test :)

(14) By J.M. Aranda (JMAranda) on 2021-09-06 19:36:30 in reply to 11 [link]

It's about Attackers. But I will follow your advice. No more cryptic jokes.
From the Lempel–Ziv–Markov chain algorithm upwards.