Am I safe against SQL injection

Question

I would like to know if I'm safe against SQL injection when I use something like that with PostgresSQL:

CREATE or REPLACE FUNCTION sp_list_name( VARCHAR )
RETURNS SETOF v_player AS '
   DECLARE
      v_start_name ALIAS FOR $1;
      r_player  v_player%ROWTYPE;
      v_temp VARCHAR;
   BEGIN
      v_temp := v_start_name || ''%'';
      FOR r_player IN
         SELECT first_name, last_name FROM v_player WHERE last_name like v_temp
      LOOP
         RETURN NEXT r_player;
      END LOOP;
      RETURN;
   END;
' LANGUAGE 'plpgsql' VOLATILE;

I want to use this function to list player's name beginning with a letter.

select * from sp_list_name( 'A' );

gives me players with last name beginning with A.

I tried to inject sql with

select * from sp_list_name( 'A; delete from t_player;--' );
select * from sp_list_name( '''; delete from t_player;--' );

Am I safe ?

Which case I could be injected ?

Regards

Answer 1

In terms of your procedure you seem safe as the variable in the SP won't be expanded into code, but you can still expose yourself if you don't use a parameterized query like "SELECT * FROM sp_list_name(?);" in your appplication code. Something like "SELECT * FROM sp_list_name('$start_name');" could be subverted by a user passing a start name of "');delete from t_player where last_name NOT IN ('". So use a parameterized query or sanity check your inputs in your program.

NB: To others, please note that a variable in a stored procedure will not expand into code even if it contains a ' or ;, (excluding passing it to EXECUTE, for which you would use quote_literal, not hand-rolled replace functions) so replacing ; or ' is totally unnecessary (in the stored procedure, the application using it is a different story, of course) and would prevent you from always finding the "tl;dr" or "O'Grady" teams.

Leo Moore, Karl, LFSR Consulting: v_temp_name in the stored procedure will NOT be expanded into code in the SP (no EXECUTE), the check would need to be done n the application, not the SP (or the OP could just use a parameterized query in their app code, instead). What others are suggesting is similar to worrying about

my $bar = "foo; unlink('/etc/password');"; 
my $baz = $bar;

actually running the unlink in the absence of an eval.