0

I'm trying to figure out if an SQLi for the following PostgreSQL/Java code exists.

public void availableItems(String name) {
  return this.query("SELECT * FROM items WHERE name='"+name+"'");
}

Assuming that in the name is removing apostrophe and semicolon. Is it possible to make a SQLi work with this restrictions? my gut feeling tell me that I could but I'm a bit lost.

4
  • If you control the contents of the name variable and there are no restrictions or modifications to that variable, then yes.
    – john doe
    Commented Jul 24, 2020 at 13:32
  • @johndoe ״Assuming that in the name is sanitizing space, apostrophe and semicolon. Is it possible to make a SQLi work with this restrictions״
    – asker asky
    Commented Jul 24, 2020 at 13:59
  • 1
    @askerasky What does "sanitize" mean in this context? Delete, offending characters, put an escape character in front? And how good is it? Assuming backslash is the escape character, it might turn fred';drop users;-- into fred\';drop users;--, which might be safe, but if it turned fred\';drop users;-- into fred\\';drop users;-- (i.e. didn't notice the backslash already there) you've now got an escaped backslash and effectively the original attack.
    – TripeHound
    Commented Jul 26, 2020 at 14:39
  • 1
    @TripeHound I meant removing (edited)
    – asker asky
    Commented Jul 26, 2020 at 20:14

1 Answer 1

1

Depending on the PostgreSQL version and configuration, it might have the "standard_conforming_strings" setting off. If it does - this is the legacy behavior - then you can also escape apostrophes using the \ (backslash) character.

IN THE SPECIFIC INSTANCE of the code you presented, this is only a mild problem; an attacker could put a backslash at the end of the "name" parameter, causing the terminating apostrophe to be escaped and thus the DB engine to see the query as containing an unclosed string (which it would reject, probably without any other harm occurring unless that query was important for some security purpose like logging or error detection).

It becomes a huge problem, though, if there are two attacker-controlled insertion points in the same query. Then, the attacker could prevent the first string from ending until reaching the apostrophe that is supposed to start the second string, and the second string would be interpreted as commands instead of data. The attacker could put whatever they want in there, including subqueries or similar to pull arbitrary data from other tables; the only limitation would be the inability to start a string or entirely new query.

Also, you say the apostrophes (and semicolons, even though those are safe within a string) are removed, not escaped, but of course that can cause its own problems - sometimes names of people or things contain apostrophes! - and if the app instead escapes them then that may also be vulnerable. The standard way to escape an apostrophe is with another apostrophe, so "O'Toole's Doodads" becomes "O''Toole''s Doodads", but if the DB allows backslashes for escaping but the application doesn't escape them itself, then you if you supply "bob' OR 1=1 --" will be converted to "bob'' OR 1=1 --" which would produce the following SQL: SELECT * FROM items WHERE name='bob\'' OR 1=1 --', which the DB would interpret as "select all fields from table [items] that have a name of bob' or that satisfy a condition which always evaluates to true, and ignore the last character because it's a comment".

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .