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.

  • If you control the contents of the name variable and there are no restrictions or modifications to that variable, then yes. – john doe Jul 24 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 Jul 24 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 Jul 26 at 14:39
  • 1
    @TripeHound I meant removing (edited) – asker asky Jul 26 at 20:14

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".


| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.