On 6/9/17, Zach C. <fxc...@gmail.com> wrote:
> I realize it's not the latest version, but I'm having an issue where the
> moment I want the sqlite3 extension to do variable interpolation, it will
> break json_extract()
>
> See this tclsh output:
>
> % mydb eval "SELECT json_extract(json, '$.hash') FROM NotImportant WHERE
> json_extract(json, '$.hash') = $someId"
Nooooooo! :-)
The argument to eval should be in curly-braces. Like this:
mydb eval {SELECT json_extract(json, '$.hash') FROM NotImportant
WHERE json_extract(json,'$.hash') = $someId}
This causes the original text to be passed down into SQLite so that
SQLite itself can do the necessary variable expansion. If you use
double-quotes, you open yourself up to an SQL injection attack. For
example, if a malicious users were to somehow set someId as follows:
set someId {NULL; DROP TABLE NotImportant;}
Then in your original SQL, where TCL is doing the variable expansion,
your entire table would be destroyed - not what you want to happen.
But if the attacker constructs the someId value as shown and you use
curly braces, then SQLite will simply look for the entry where
json_extract(json,'$.hash') equals the string contained in $someId.
No extra SQL is run and you are completely safe from SQL injection.
Bottom line: Unless you are an expert, and know exactly what you are
doing, and have a compelling reason to do otherwise, *always* put the
argument to the eval method inside of curly brances.
Please go back through all your code and fix that. Now. It's important.
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
