0

Trying to exploit SQL injection for my assignment. Is it possible to execute delete or drop query after order by in select query without using the semicolon in Postgresql?

This is my sample query:

Select * 
from table 
order by {sql injection payload}

Without using the semicolon in the payload, can we delete data or drop a table?

https://stackoverflow.com/a/6800585

Do we have similar to this Postgrsql?

I tried

Select * from (delete from table_name returning *) a

But getting sql error as 'syntax error at or near from'

Fuyda
  • 1
  • 1
  • If you are not ending the statement with a semicolon (`;`) then this will always result in a syntax error. –  Nov 27 '22 at 10:09
  • @a_horse_with_no_name Is there any way like by passing query as expression in case statements or delete query with returning something or any other way to end query without semicolon. – Fuyda Nov 27 '22 at 20:27

2 Answers2

0

Check this document it says we can bypass forbidden character by CHR()

https://book.hacktricks.xyz/pentesting-web/sql-injection/postgresql-injection

Hirxe
  • 1
  • 2
  • Tried it but not working like select * from table_name order by (select 1||chr(59) ||(delete from table other_table_name)) got sql error saying ' syntax error at or near from' – Fuyda Nov 27 '22 at 19:45
0

DELETE cannot be put inside a subquery. Nor can DELETE be part of a UNION.

So aside from running a second query (that is, separated by a semicolon), there's almost no way you can do what you describe.

You could invoke a stored procedure or function, if you knew of an existing function that performs a DELETE. Example:

Select * 
from table 
order by {sql injection payload}

After your payload modifies this query:

Select * 
from table 
order by SomeFunctionThatDeletes()

Another type which works because you can select from a procedure in PostgreSQL:

Select * 
from table 
order by id
UNION
Select * 
from SomeProcedureThatDeletes()

You can't create the function or procedure with SQL injection, so that routine must exist already, and you would need to know its name and how to call it.

DELETE or DROP TABLE are not the only bad things that can happen from SQL injection. It could be a problem if the query returns data that the current user shouldn't have privilege to see. For example, records about a different user's purchases or medical history.

SQL injection can also be accidental instead of malicious. I would even say that most instances of SQL injection result in simple errors instead of data breaches. Those aren't really attacks, but they lead to an unsatisfactory experience for your users.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • union is not working after order by. I created query as select * from table_name order by id union select * from table_name. It gives sql error saying 'syntax error at or near union – Fuyda Nov 28 '22 at 04:32
  • Ah, I forgot if you use UNION, you can only ORDER BY at the very end. There's no point to ordering the intermediary queries. Okay, then you can't do that. – Bill Karwin Nov 28 '22 at 05:54
  • Good answer. Don't forget that you can create temporary functions in `pg_temp` if you have the permission on the database. – Laurenz Albe Nov 28 '22 at 07:05
  • @LaurenzAlbe Can an attacker create a temporary function as part of an expression or a subquery by using SQL injection? I don't know of any way to do that. – Bill Karwin Nov 28 '22 at 07:18
  • @BillKarwin Hmm, true, that is not possible; certainly not without injecting a semicolon. – Laurenz Albe Nov 28 '22 at 07:24