Demonstrate Sql Injection In Pl/pgsql

I have this function in plpgsql: CREATE OR REPLACE function login_v(em varchar, passwd varchar) RETURNS users AS $$ DECLARE cu users; BEGIN SELECT * into cu FROM users w

Solution 1:

SQL queries in PL/pgSQL are planned like prepared statements. As long as you only pass values like you do, SQL injection is generally impossible. Details:

• SQL injection in Postgres functions vs prepared queries

Use dynamic SQL with EXECUTE and without proper parameter handling to actually demonstrate SQL injection.

Like (this is how not to do it!):

CREATEOR REPLACE FUNCTION login_v(em varchar, passwd varchar)
  RETURNS SETOF users AS
$func$
BEGINRETURN QUERY EXECUTE'SELECT *
         FROM   users
         WHERE  email = $1
         AND    encrypted_password = crypt('''|| passwd ||''', encrypted_password)'USING em;
END
$func$  LANGUAGE plpgsql;

The first variable em is properly passed with the USING clause as value and thus cannot be abused for SQL injection.

But the second variable passwd is improperly concatenated without properly escaping. Thus, user input can be converted to SQL code. SQL injection.

Never use this! Except when demonstrating how not to do it.

Similar mischief is possible when concatenating SQL strings in the client improperly.