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 where email = em
AND encrypted_password = crypt(passwd, encrypted_password);
return cu;
END
$$ LANGUAGE plpgsql;
When I provide an input like this: select login_v('[email protected]'' OR 1=1;--','la la la');
, I think my method should return the user with email [email protected]
. What Am I doing wrong?
Performing SQL injection is necessary here to demonstrate the concept for an exercise, but I am an SQL injection and plpgsql boob. :|
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:
Use dynamic SQL with EXECUTE
and without proper parameter handling to actually demonstrate SQL injection.
Like (this is how not to do it!):
CREATE OR REPLACE FUNCTION login_v(em varchar, passwd varchar)
RETURNS SETOF users
LANGUAGE plpgsql AS
$func$
BEGIN
RETURN QUERY EXECUTE
'SELECT *
FROM users
WHERE email = $1
AND encrypted_password = crypt(''' || passwd || ''', encrypted_password)'
USING em;
END
$func$;
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 concatenated without quoting properly. Thus, user input can be converted to SQL code. SQL injection.
Never do this! Except when demonstrating how not to do it.
Similar mischief is possible when concatenating SQL strings in the client improperly.