Short answer, no. The quoting trick is easily defeated by including your own closing quote and then a comment symbol to eliminate the final concatenated quote, precisely as in your example.
To protect yourself from SQL injection you must use bind variables. Changing your example to SELECT * FROM table WHERE ID = :X and then binding the user's input to X solves the problem instantly and completely. It is impossible to over-emphasize how important this practice is!
This uses the same basic technique as jonearles (+1) (since removed) but eliminates the PIVOT and would require only one function that turns the string into a CASE statement.
SELECT substr(
MIN (
CASE WHEN Blank=1 THEN '1 Blank'
WHEN Error=1 THEN '2 Error'
WHEN InProgress=1 THEN '3 InProgress'
WHEN Completed=1 THEN '4 Completed'
END
)
,3) state_code
FROM m_object mo
JOIN m_form mf ON mo.id = mf.id
WHERE mo.v = :2 AND mf.formlayout_id = :1;
I assume the data looks something like this:
drop table m_object;
create table m_object as
(select 10 id, 0 blank, 0 error, 0 inprogress, 1 completed, 99 v from dual);
insert into m_object values (11,1,0,0,0,99);
insert into m_object values (12,0,0,0,1,99);
insert into m_object values (13,0,1,1,0,99);
drop table m_form;
create table m_form as (select 10 id, 20 formlayout_id from dual);
insert into m_form values (11,21);
insert into m_form values (12,22);
insert into m_form values (13,20);
Here is what the function might look like if it were done in PL/SQL. It was just thrown together and is only meant to illustrate building the SQL statement and is not meant to be an example of good coding.
set serveroutput on format wrapped
DECLARE
vPassed Varchar2(500) := 'Blank,Error,InProgress,Completed';
Function MakeCase(pOriginal In Varchar2) Return Varchar2 Is
vBuilt Varchar2(500);
vWord Varchar2(100);
vChar Char(1);
vWordCount Number(1) := 1;
Begin
For vLoop In 1..Length(pOriginal) Loop
vChar := substr(pOriginal,vLoop,1);
If (vChar = ',') Then
If (vBuilt IS NULL) Then
vBuilt := 'CASE WHEN ';
End If;
vBuilt := vBuilt || vWord || '=1 THEN '''
|| to_char(vWordCount,'FM0') || ' ' || vWord || ''' WHEN ';
vWord := '';
vWordCount := vWordCount + 1;
Else
vWord := vWord || vChar;
End If;
End Loop;
vBuilt := vBuilt || vWord || '=1 THEN '''
|| to_char(vWordCount,'FM0') || ' ' || vWord || ''' ';
vBuilt := vBuilt || ' END ';
Return vBuilt;
End;
BEGIN
vPassed := MakeCase(vPassed);
DBMS_Output.Put_Line(vPassed);
--Use vPassed here to build SQL statement.
END;
/
Best Answer
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:
This means that "P" is now the decimal point and "Z" is the thousands separator. So:
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:
Also create a function P01 which does something undesirable (in this case just creating a table, but you get the idea):
And we're good to go:
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_formatcan 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.