SQL injection on PostgreSQL

Question

My clienet(android) sends user details in the user form and my servlet just enters those details into to the database(postgre sql). I tried to give to do a sql injection attack by giving ;DELETE FROM tbl_name; in the username field.
But postgresql just treats it as a value and enters it as the username. How do I do the SQLINjection attack. (I have not done any sort of checking in the postgre sql or the servlet).
Does it mean that postgresql is SQLInjection attack resistant?

I am using the following statements to insert the data:

String insert ="insert into userdetail(username,id,sno) values('"+username+"','"+userid+"','"+no+"')";
                   Statement stmt = conn.createStatement();
                    stmt.executeUpdate(insert);

The username contains ;DELETE FROM userdetail;.
I have tried the following also:

');DELETE FROM userdetail;

But it fives the following error:

org.postgresql.util.PSQLException: ERROR: unterminat
ed quoted string at or near "');"
  Position: 1

I have also tried this:

','',');DELETE FROM userdetail;

This gives the following error:

17:36:46,828 INFO  [STDOUT] org.postgresql.util.PSQLException: ERROR: unterminat
ed quoted string at or near "''');"
  Position: 38

but does not delete the records of the table. How do I make it delete the tables records?

Postgresql is not SQLInjection resistant, you are probably not using the single quote (') properly. Could you please the exact string you are trying and the query it is building to insert in the database — Habib
– Habib, Commented Mar 16, 2012 at 9:26

A.H. · Accepted Answer · 2012-03-28 17:49:42Z

5

The key trick is that the complete statement string must

do something innovative
be still valid SQL

So far the answers have omitted the second part. But an invalid SQL statement will abort the transaction and hence most likely does nothing at all. If you set autoCommit to true that attack may be easier.

But this string should to the trick in a "clean" way:

foo', '42', '42'); delete from userdetail; --

Note: The resulting string is this (line breaks only for better reading):

insert into userdetail(username,id,sno) values('foo', '42', '42'); 
delete from userdetail; 
-- ','21','21')

Both the INSERT part is complete and correct (assuming no unique index collisions of course) and also the DELETE is correct. The potentially offending rest is masked by the trailing SQL comment --.

edited Mar 28, 2012 at 17:49

answered Mar 16, 2012 at 16:53

A.H.

66.8k16 gold badges98 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ashwin Over a year ago

:The SQL comments did the trick. Without the comments it gives give unterminated quoted string error.

A.H. Over a year ago

@user1139023: The SQL comment is indeed the central point in "be still valid SQL".

score 2 · Accepted Answer · 2012-03-16 10:33:42Z

2

hat type of attack that you are describing - even if you get the use of single quotes "correct" - will not work with PostgreSQL It does not work because the JDBC driver does not allow to run more than one statement in a single Statement.execute() call. It will throw an error ("invalid character" pointing to the ;)
(Sorry, this is only true for Oracle)

There are other scenarios that would work with a badly written application.

Assuming the application is checking the username/password like this (note that this is a very simplified example!)

String sql = "SELECT count(*) FROM users WHERE username = '";
sql += username;
sql += "' AND password = '";
sql += pwd;
sql += "'"

then a possible attack could be to enter the value:

' or 1=1 or '' = '

into the password field.

This would wind up with the following generated SQL

SELECT count(*) FROM users WHERE username = 'arthur' 
AND password = '' or 1=1 or ''=''

Which would always be true and one could login without a password.

edited Mar 16, 2012 at 10:33

answered Mar 16, 2012 at 9:47

user330315

2 Comments

Ashwin Over a year ago

You can more than one single statement in jdbc. My application requires that the userdetails be inserted into two tables. I do that By Using a single executeUpdate statement. The string inside passed as the argument to the executeUpdate statement contains two insert statements seperated by ';'.

user330315 Over a year ago

@user1139023: hmm, interesting. I'm pretty sure I have seen this fail with Postgres. But maybe this was Oracle after all. Thanks for the hint.

belgther · Accepted Answer · 2012-03-16 14:47:57Z

1

Try ', '', '');DELETE FROM tbl_name; to delete your table.

In simplest case, you can give ' as user name and your server should give you an error.

Besides, there is no SQL injection resistant database because SQL injection happens because of poorly coded server-side (CGI) script and not because of database weaknesses.

edited Mar 16, 2012 at 14:47

answered Mar 16, 2012 at 9:53

belgther

2,53417 silver badges15 bronze badges

5 Comments

Ashwin Over a year ago

I will try that and let you know.

Ashwin Over a year ago

comma is not allowed in any of the fields in my app. Can I remove the comma's and try it.

belgther Over a year ago

practically, you can. But be wary that the server might give an error that your INSERT statement is invalid which refers to a successful SQL injection.

Ashwin Over a year ago

I tried '''');DELETE FROM userdetail; .It still takes it as a value and inserts '''');DELETE FROM userdetail; as the username.

Ashwin Over a year ago

Shouldn't it be ', '', '');DELETE FROM tbl_name; one more Single quote before the bracket. Because there are three columns in the table.

Collectives™ on Stack Overflow

SQL injection on PostgreSQL

3 Answers 3

2 Comments

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related