1

I am trying to write an object-oriented layer for creating SQL queries for NodeJS, like Zend's DB component. So I want to be able to escape values. Yes, I know parameterized queries are ideal. But reasons I want escaping:

  • I want to allow the created queries to be executed driver-agnostically (in terms of creating a platform-specific SQL string, instead of relying on one library's interface for parameterized queries), although I will add support for prepared queries for popular drivers
  • Zend's Db component does escaping, so it seems like it should be possible (safe)
  • There is nothing like it for NodeJS I am aware of, so I can't find any examples or use another library's escape function

Since Zend uses it, I started with this approach. I found examples for escaping strings for MySQL and PostgresSQL, but didn't find anything for Sqlite3.

I tried tracing PHP's Sqlite3::escapeString function, and ended up at sqlite3VXPrintf in sqlite3.c, line 19016, which is a massive printf function. I'm not even sure that's where I was supposed to end up. Anyways, I had no luck trying to understand it :P

So I'm hoping someone can give me more pointers as to how to do this. And what do I have to be aware of to make my escaping functions safe? Why isn't replacing all ' with \\' safe? I'm aware of flaws like second order SQL injection, but that's still because someone forgot to escape something. An interface for creating SQL queries could automatically quote values, unless the developer explicitly says "this is safe"

While I'm at it, regarding the solution in the earlier MySQL question, it looks pretty simple (no special cases?). Is that really all there is to it? Assuming unsafeinput is always escaped. mysqli_real_escape_string escapes NUL (ASCII 0), \n, \r, \, ', ", and Control-Z. Sqlite3's documentation only mentions single quotes, but certain characters like NUL look fishy. Anywhere I can find out more about this?

PostgresSQL's escape function in libpq also looks like it's just replacing quotes with escaped quotes. I guess I'm just making sure this is okay because I always hear "use parameterized queries!" as if escape functions are useless.

TLDR: I have two main questions

  1. Is escaping special characters for queries safe if it is done properly and everywhere?
  2. Where can I find examples of Sqlite3 escaping code? The official docs only mention the single quote for literals (want to make sure), and I had no luck tracing PHP source.
Community
  • 1
  • 1
Raekye
  • 5,081
  • 8
  • 49
  • 74
  • Is it possible you're overthinking this? "Just parameterize your queries" is likely the best possible answer, and the `Sqlite3::escapeString` should do exactly what you are describing here. So what's the problem? – Robert Harvey Jul 01 '13 at 04:47
  • @RobertHarvey `Sqlite3::escapeString` does what I want, but in PHP (I realized I wasn't explicit - I am writing a module for NodeJS) – Raekye Jul 01 '13 at 04:49
  • Oh. Hold on a moment. – Robert Harvey Jul 01 '13 at 04:49
  • OK. Well, the only reference to "escaping strings" on the SQLite web site is at http://www.sqlite.org/lang_expr.html, about halfway down the page, and it refers to using two single quote characters in succession to escape a single quote, and *that is all.* Escaping strings doesn't necessarily protect you from SQL injection, and all of the references that I am finding on Google say to use Parameterization; that's what I suggest you do. – Robert Harvey Jul 01 '13 at 04:54
  • @RobertHarvey what's a case when escaping won't protect from sql injection? (Trying not to stray far off the original question, but luckily I did brush on "is that all to it" ;) ) – Raekye Jul 01 '13 at 04:57
  • [`Robert; drop table students`](http://xkcd.com/327/) – Robert Harvey Jul 01 '13 at 05:00
  • @RobertHarvey in the comic there is a single-quote - but if escaped it would be entered as one entire String. It's possible to suffer from second order attacks if you're not careful, but that doesn't mean escaping itself is still liable. Zend is a massive framework, and I see them quoting https://github.com/zendframework/zf2/blob/master/library/Zend/Db/Adapter/Platform/Mysql.php#L111 ; surely there isn't a security flaw? – Raekye Jul 01 '13 at 05:03
  • I'm still trying to figure out why you want to spend time on this when you can just parameterize and be done with it. :) – Robert Harvey Jul 01 '13 at 05:04
  • Related: http://stackoverflow.com/q/603572/102937 – Robert Harvey Jul 01 '13 at 05:06
  • @RobertHarvey because I'd prefer to make allow this module to be sql-driver agnostic (e.g., not rely on one person's PostgresSQL module). I plan on adding support for that - so built query objects can be executed as prepared statements, but I included a Zend link - since they seem to be doing it (quote values to produce a raw SQL string) - I'm believing it's possible. (do I make sense? is there a better way?) – Raekye Jul 01 '13 at 05:06
  • @RobertHarvey Thanks for the link, I didn't find that. Good for confidence, but it's not really clear whether that's all that needs to be done to be safe – Raekye Jul 01 '13 at 05:09
  • You might want to clarify that your question is about sanitizing the inputs to a generic database driver, and not about SQLite or PostGres specifically. – Robert Harvey Jul 01 '13 at 05:11
  • Thanks, I updated the title and made some edits. I left the sqlite tag though as at least I have mysql and postgres examples to sorta "be more confidant" on, and Sqlite is my next goal. If someone really proves that escaping is impossible, then I'll resort to relying on other libraries and sending parameterized queries (I should also send a note to Zend and tell them to stop using escaping in the Db code :) ) – Raekye Jul 01 '13 at 05:20
  • `"I'd prefer to make allow this module to be sql-driver agnostic"` - this is what you are doing wrong. Anyways, I wish you good luck and a ton of spare time. You will need them A LOT. – Your Common Sense Jul 01 '13 at 05:40
  • @YourCommonSense haha I'm trying my best not to reinvent the wheel. But noone's given a concrete example - and if escaping is so bad, why is Zend doing it (I'm guessing, and this is why I'm doing it, it's because an API for creating queries can automatically escape all values, unless the developer explicitly says "this is safe") – Raekye Jul 01 '13 at 05:48
  • I dunno why you mentioned my nickname as your comment was obviously to someone else. – Your Common Sense Jul 01 '13 at 06:04
  • But if you said so - Zend indeed doing *it*: `trigger_error( 'Attempting to quote a value in ' . __CLASS__ . ' without extension/driver support ' . 'can introduce security vulnerabilities in a production environment.' );` – Your Common Sense Jul 01 '13 at 06:07
  • @YourCommonSense no it was meant for you :P I don't think 'making a driver agnostic sql library' (in terms of producing valid sql strings for a given vendor, not producing universal sql strings) is wrong, but I can see how it may seem like reinventing the wheel? Anyways, sorry if I'm not making sense – Raekye Jul 01 '13 at 06:07
  • @YourCommonSense it is using the drivers for escaping, and not parameterizing (i.e., the driver is doing some sort of search and replace down the line). It only triggers the error if there is no driver (if I understand correctly that you're saying Zend isn't escaping) – Raekye Jul 01 '13 at 06:09
  • ...if there is no **known** driver. And it's not an error but just a warning, while it still tries to escape for the unknown database - exactly what you are truing to do. So, your Zend does warn **you** – Your Common Sense Jul 01 '13 at 06:11
  • @YourCommonSense I'm not sure I understand your point. My reasoning is Zend is escaping strings (not using parameterized queries), so that's probably okay. I am able to find the source for these drivers for MySQL and PostgresSQL, so I was able to port them to Javascript. I can't find source for Sqlite3, so I'm looking for pointers (and double checking with the community that escaping is okay, although 99% of the cases parameters are the right choice) – Raekye Jul 01 '13 at 06:15
  • Zend is escaping strings but when doing it **in database agnostic mode** it **throws a warning**, which can be clearly seen in the code you linked to. So I told you. I see nothing to argue of. Ask Zend to remove that warning first. – Your Common Sense Jul 01 '13 at 06:27
  • @YourCommonSense ah I see, sorry for the confusion. I edited my question for a clarification of "driver agnostic" – Raekye Jul 01 '13 at 06:43
  • If your question is "how to escape strings for Sqlite", why not to ask this very question without all this long and windy offtopic on "generic sql strings" (as such a thing just doesn't exist)? – Your Common Sense Jul 01 '13 at 08:09
  • @YourCommonSense haha earlier Robert recommended making it more generic. And also if I didn't explain why I needed SQL strings (I can't hardcore to on specific driver's interface) everyone would tell me to use parameterized queries. – Raekye Jul 01 '13 at 15:43
  • There are parameterized queries and parameterized queries. As far as I know, sqlite does not support native prepared statements. So, a driver have to concatenate anyway. Though it doesn't mean that at user level a parameterized query have to be used. So, there is no contradiction at all – Your Common Sense Jul 01 '13 at 15:53
  • @YourCommonSense I'm not sure I get your point, but 1. Thanks for the tip - I didn't know that. So I should be able to look for other drivers and I should be able to find what they do. 2. This guy asked a similar question http://stackoverflow.com/questions/4820374/sqlite-escape-string-c and the terse answer was "No. Use bound parameters." (if that's not the same as a parameterized queries that still doesn't work for me, because as I said I can't hardcode for one person's interface) – Raekye Jul 01 '13 at 16:03
  • Well, my apologies - it seems sqlite indeed does use native prepared statements. So you have to. – Your Common Sense Jul 01 '13 at 16:08
  • For SQLite, you can use the quote function; it's implementation lives in the source file func.c; it seems that indeed it does little more than double the quotes – Mark VY Sep 19 '16 at 01:56

0 Answers0