I am slowly coming to terms with Oracle again, after a decade or so of using SQL Server exclusively. One way to guard again SQL injection in SQL Server is to use parameterisation and sp_executesql.

From Raul Garcia’s example:

  declare @cmd nvarchar(max)
  declare @parameters nvarchar(max)
  set @cmd = N'SELECT * FROM sys.database_principals WHERE name = @name'
  set @parameters = '@name sysname'
  EXEC sp_executesql @cmd, @parameters, @name = @name 

To do this parameterisation in Oracle (I am using 10g) you can use bind arguments, as per this code snippet:

  l_username := 'dav';
  OPEN l_cursor FOR
    'SELECT *
     FROM myTable t
     WHERE 
       t.created_by LIKE :username || ''%'''
     USING l_username; 
  -- fetch from, then close cursor --

Here :username is bound to the value in l_username via the USING clause, so no tricky '''; DROP TABLE myTable; --' style nonsense here, thanks very much :).