1

Is the following function immune to SQL attacks? (I'm using Node.js and 'pg'). s is any string that a user provides (it can have malicious intent).

import pg from 'pg' // import postgres nodejs

var readFromDB = (s) => {
  s = doubleEscapeSingleQuote(s);
  const dbQueryString = `SELECT * FROM table WHERE id = '${s}'::int8`
}

I really think this is immune to all SQL injections. Any single quotes in s get double-escaped, and then the whole thing is surrounded by single quotes, so it will always be interpreted as a string and then converted back to an int. If there's any error then the query will fail as it should.

I'm sure it's best practice to use $1 notation instead, but I really don't see a point in doing that if this is totally valid. Can you let me know if there's actually a problem with what I'm doing?

Lucas Mumbo
  • 162
  • 8
  • It seems the above which I linked to just says "it's fine, but bad practice"... is that right? – Lucas Mumbo Apr 04 '22 at 05:43
  • Thanks, you too. I found this link to the duplicate helpful cause I had the same query. Basically `pg8000` which is linked from google cloud functions documentation uses `literal` https://github.com/tlocke/pg8000/blob/main/pg8000/converters.py#L719 which simply escapes single quotes and I was wondering if it was even safe. – jamylak May 05 '22 at 04:09
  • I mean it's a simple logical argument right? The only way to inject code is to end the string. The only way to end the string is with a closing apostrophe. The only way to inject a closing apostrophe is to type it in. But any apostrophe gets escaped, so it's impossible. QED. The only other possibility would be buffer overflows which circumvent that. The only questionable statement I made is "The only way to end the string is with a closing apostrophe. " but it seems reasonable. – Lucas Mumbo May 05 '22 at 05:50
  • 1
    The attacks mentioned were mostly to do with string truncation though there may be some other attacks that target specific flavours of sql or some new way in the future. I guess it's always good to sanitise where possible but it does seem to me like, accounting for the above, it seems to cover everything. I should check into how some bigger libraries are doing it as well – jamylak May 05 '22 at 23:07

0 Answers0