Timeline for Is single quote filtering nonsense?
Current License: CC BY-SA 4.0
16 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Feb 8, 2019 at 14:59 | comment | added | AJMansfield | @IMSoP Oh, whoops! Actually didn't meant to tag anyone with it... | |
| Feb 8, 2019 at 14:57 | comment | added | IMSoP |
@AJMansfield OK, I was confused because your comment was addressed to supercat, so I though it related to their "select * where id is in [list]" example, rather than to the answer (which was written by someone else).
|
|
| Feb 8, 2019 at 14:55 | comment | added | Joshua | I once did this. Every single user string was htmlencoded immediately. I now believe it is a poor architectural choice. | |
| Feb 8, 2019 at 14:51 | comment | added | AJMansfield |
@IMSoP It's in reference to the example linked in the answer. I'm just using <token> to mean whatever prepared statement syntax your SQL driver needs for the actual operation. As for a user-supplied IN clause, that's not really what I'm getting at; rather in the linked example the second-order SQL injection it seems like it's mostly just a consequence of using some sort of janky string expansion mid-query. I'm no expert DBA but it doesn't exactly make a very good case for why you might need to run a query that would be vulnerable to that issue in the first place.
|
|
| Feb 8, 2019 at 4:24 | comment | added | AJMansfield |
@supercat Wouldn't you just do something like select username from usertab join sessiontab on usertab.id=sessiontab.id where sessions.token=<token>? Maybe performance, I guess, but a full join like that gets optimized away by the query planner anyway and even if it didn't for some reason you can still force it by joining to a subquery instead.
|
|
| Feb 6, 2019 at 22:01 | comment | added | jpmc26 |
@supercat I don't know about psycopg2 specifically, but I do know that some client libraries expand the array out into individual parameters for some databases, like IN (:p1, :p2, :p3...). So that's at least one technique.
|
|
| Feb 6, 2019 at 21:58 | comment | added | jpmc26 |
PostgreSQL provides a vastly better defense against injection into EXECUTE IMMEDIATE queries: a set of functions that will properly quote the input. They are quote_ident, quote_literal, and quote_nullable. Obviously, avoiding EXECUTE IMMEDIATE is preferred, but these are worlds better at sanitizing if you must use it.
|
|
| Feb 6, 2019 at 0:21 | comment | added | Peteris | @supercat I can't be certain without looking more into psycopg2 internals than the API documents say, but as arrays are a native postgresql data type (postgresql.org/docs/9.0/arrays.html) that's possible (and thus necessary to support) as a value for columns in any random SELECT, then I'd assume that the driver must support exchanging arrays in a proper form, not some stringified hack. | |
| Feb 5, 2019 at 22:51 | comment | added | supercat | @Peteris: Is the Psycopg library passing arrays to the SQL engine as parameters, or is it expanding them out as a command which takes a variable number of individual parameters, or what? | |
| Feb 5, 2019 at 21:48 | comment | added | Peteris | @supercat it certainly can be done - for example, for python/postgres this is done like initd.org/psycopg/docs/usage.html#lists-adaptation or initd.org/psycopg/docs/usage.html#tuples-adaptation , so there's some driver support and similar features should be available for other languages/DB engines. | |
| Feb 5, 2019 at 19:10 | comment | added | supercat | @tomsmeding: Unless things have changed, there isn't any nice way to execute something of the form "select * where id is in [list]" without using dynamically-generated SQL to specify the list. I wouldn't try such a thing with user-specified items in the list, but with numeric IDs processed using numeric data types I don't see any injection risk. | |
| Feb 5, 2019 at 10:03 | comment | added | Erik A |
@tomsmeding Second-order injection is possible in most SQL dialects (T-SQL through sp_executesql, PL/SQL through EXECUTE IMMEDIATE, MySQL through EXECUTE). They all support parameters, so you can do it properly, but there are limitations. For example, I've migrated Access SQL pivot queries to T-SQL, and the only way to do this in T-SQL is to use string concatenation to achieve dynamic field names like Access does (with escape characters, but escape characters are bad, and I've encountered example scripts without them), since you can't use parameters for field names.
|
|
| Feb 5, 2019 at 9:53 | comment | added | tomsmeding |
Admittedly I'm not well-versed in pentesting or PL/SQL (which I assume this is about) in general, but second-order SQL injection seems really horrible. If we collectively have learned that using eval() on user input is not a good idea, why then do we ever use that kind of construct in PL/SQL? I hope there is another way to do that query behind your link that does respect the difference between code and data.
|
|
| Feb 4, 2019 at 15:25 | review | First posts | |||
| Feb 4, 2019 at 16:21 | |||||
| Feb 4, 2019 at 15:25 | history | edited | Erik A | CC BY-SA 4.0 |
added 98 characters in body
|
| Feb 4, 2019 at 15:20 | history | answered | Erik A | CC BY-SA 4.0 |