Home

SQL Injections & its remedies

What is SQL Injection ?

SQL injection is a security loophole whereby the developer accepts input from an end user and concatenates that input into a query, then compiles and executes that query. In effect, the developer accepts snippets of SQL code from the end user, then compiles and executes those snippets. That approach allows the end user to potentially modify the SQL statement so that it does something the application developer never intended.
It’s almost like leaving a terminal open with a SQL Plus session logged in and connected as SYSDBA. Many of us are unaware of how SQL queries can be tampered with, and assume that an SQL query is a trusted command. Thereby bypassing standard authentication and authorization checks.

Although any program or application may be vulnerable to SQL injection, Web applications are at higher risk because an attacker can perpetrate SQL injection attacks without any database or application authentication.

I have demonstrated this with few examples below :

Demonstration 1

Consider the below procedure :

SQL> CREATE OR REPLACE PROCEDURE User_Login (
p_Email      Employees.Email%type      DEFAULT NULL,
p_Last_Name  Employees.Last_Name%type  DEFAULT NULL)
AS
Stmt CONSTANT VARCHAR2(4000) :=  ‘SELECT Email FROM Employees
WHERE   Email = ”’ || p_Email || ”’
AND    Last_Name = ”’ || p_Last_Name || ””;
v Employees.Email%type;
BEGIN
DBMS_Output.Put_Line (‘SQL Stmt: ‘ || Stmt);
EXECUTE IMMEDIATE Stmt INTO v;
DBMS_Output.Put_Line (‘Logon succeeded.’);
EXCEPTION WHEN OTHERS THEN
Raise_Application_Error(-20000, ‘Logon failed.’);
END User_Login;
/
Procedure created.

A normal user who has privileges to execute will use this procedure as

SQL> EXEC User_Login(‘PFAY’,’Fay’);
Logon succeeded.

Now an evil developer(X) may enter malacious parameters to unauthorised access and logon. This is how:

SQL> EXEC User_Login(”’ or 1=1 and Rownum=1 –‘,’cookedLastname’);
Logon succeeded.

Therefore, user X has now illegally gained access. Lets look deeper. In this case query for user X becomes

SQL> SELECT Email FROM Employees
WHERE   Email = ”””
OR 1=1 and Rownum=1 –   Last_Name = ”’cookedLastname” ;

Here Last_Name = ”’cookedLastname” part is commented and 1=1 is makes condition in where clause true. Therefore, User X now has stolen username, password details for all users which he is not authorized to view.

Demonstration 2

Consider the below procedure :
SQL > CREATE OR REPLACE PROCEDURE injection (p_date IN DATE)
AS
l_rec     all_users%ROWTYPE;
c         SYS_REFCURSOR;
l_query   LONG;
BEGIN
l_query := ‘
select *
from all_users
where created = ”’ || p_date || ””;
DBMS_OUTPUT.put_line (l_query);
OPEN c FOR l_query;
FOR i IN 1 .. 5
LOOP
FETCH c INTO l_rec;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.put_line (l_rec.username || ‘…..’);
END LOOP;

CLOSE c;
END;
/
Procedure created.

Most of us might find it safe from SQL injection since input to the proc is ORACLE date. However, this code can be modified at runtime easily.
Suppose the evil developer(X) have access to execute that procedure, to see the users created in the database today, but he doesn’t have access to any of the other tables in the schema that owns this procedure. Lets say User_Password(uname, passwd) is one such table in this schema which is hidden from every user except SYSDBA.
X may execute below statements to get access to hidden table User_Password :

SQL> alter session set nls_date_format = ‘””union select tname, null,0 from tab”‘;
Session altered.
SQL> exec Y.injection( sysdate );

The code which ideally should execute as
Select * from all_users where created = ’28-NOV-2012′ ;
– Displays all users created on ’28-NOV-2012′
instead executes as
Select * from all_users where created = ” union select tname,null,0 from tab;
– Displays all names in database (e.g. User_Password)

Further to fetch columns of table, X again executes

SQL> alter session set nls_date_format = ‘””union select tname||-||cname,null,0 from dba_tab_cols”‘;
Session altered.
SQL> exec Y.injection( sysdate );
This executes as
Select * from all_users where created = ” union select table_name||-||column_name,null,0 from dba_tab_cols;
– Displays Columns of table with table name (e.g. User_Password-uname,,0 ; User_Password-passwd,,0)

Finally user X executes the below query to fetch data

SQL> alter session set nls_date_format = ‘””union select uname||-||passwd,null, 0 from User_Password”‘;
Session altered.
SQL> exec Y.injection( sysdate );
This executes as
Select * from all_users where created = ” union union select uname||-||passwd,null, 0 from User_Password;
– Displays username & password of table User_Password (e.g. Y-pass1,null,0 ; X-pass2,,0)

Therefore, User X now has stolen username, password details for all users which he is not authorized to view. Imagine id user 
Again a MAJOR SECURITY LAPSE !

Vulnerability assessment flowchart*

vulnerability assessment

*source: oracle documentation

Vulnerable Applications :

Almost all SQL databases and programming languages are  potentially vulnerable : MS SQL Server, Oracle, MySQL, Postgres, DB2, MS Access, Sybase, Informix, etc
Applications developed using: Perl and CGI scripts that access databases , ASP, JSP, PHP, XML, XSL and XSQL; Javascript ,VB, MFC, and other ODBC-based tools and APIs .DB specific Web-based applications and API’s
Reports and DB Applications

Avoiding SQL Injections : 

To immunize our code against SQL injection attacks, we must :
1. use bind arguments (either automatically with static SQL, or explicitly with dynamic SQL).
2. validate and sanitize all input concatenated to dynamic SQL.

I have illustrated how we could have avoided SQL Injection attack in our Demonstration 1.

Avoidance – demonstration 1

Consider the below procedure
SQL> CREATE OR REPLACE PROCEDURE User_Login (
p_Email      Employees.Email%type      DEFAULT NULL,
p_Last_Name  Employees.Last_Name%type  DEFAULT NULL)
AS
Stmt CONSTANT VARCHAR2(4000) :=  ‘SELECT Email FROM Employees
WHERE   Email = :1
AND    Last_Name = :2′;
v Employees.Email%type;
BEGIN
DBMS_Output.Put_Line (‘SQL Stmt: ‘ || Stmt);
EXECUTE IMMEDIATE Stmt INTO v using trim(p_Email), trim(p_Last_Name);

DBMS_Output.Put_Line (‘Logon succeeded.’);
EXCEPTION WHEN OTHERS THEN
Raise_Application_Error(-20000, ‘Logon failed.’);
END User_Login;
/
Procedure created.

A normal user will use this procedure as :

SQL> EXEC User_Login(‘PFAY’,’Fay’);
Logon succeeded.

Now when an evil developer(X) enters malicious parameters to unauthorized access and log on. This is how:

SQL> EXEC User_Login(”’ or 1=1 and Rownum=1 –‘,’cookedLastname’);
Logon failed.
Looking deeper, it executes as
SQL> SELECT Email FROM Employees
WHERE   Email = :1

Last_Name= :2
Error stack : ORA-1403: no data found

Hence Attack has been avoided !!

“Let us not look back in anger or forward in fear, but around in awareness.” — James Thurber

Social Media – A Genie powered by technology

Andreas Kaplan and Michael Haenlein define social media as “a group of Internet-based applications that build on the ideological and technological foundations of Web 2.0, and that allow the creation and exchange of user-generated content.” i.e. Social Media are social software which mediate human communication.

With every passing day there is a greater buzz about social media and its impact. But I see ‘Social Media as a Genie powered by technology‘ who fulfills our desires for knowledge on technology, news & updates, trending topics, startups, staying in touch with family/friends and lot more. Twitter, Facebook, LinkedIn and Google+ are on top of list by number of registered users*.

There are lots of advantages it brings along with it for people and institutions from every walk of life – students, professionals, governments, media houses, recruiters, awareness campaigns.

1. Students and budding professionals like me use twitter to connect to and follow experts and granddaddy’s of my area of interests(AOI). We can get a 30,000ft view of the landscape right in our pockets and desks. We get to know about what are vitals in our AOIs, in which direction we should work upon and where do we fit in the larger picture. It is being used to network with professionals with same interests, recruiters to find talent and marketeers to spread their word. It has been very effectively used by likes of @ShashiTharoor, @AnandMahindra@narendramodi, @janlokpal to get in touch with masses.

2. Social media is a great tool in hands of governments and its institutions. For example – Delhi Traffic Police, Traffic Police Chandigarh are using it very effectively to connect with people, give traffic updates on the go, route change information, challan the offenders. It also helps building faith and trust among citizens. Facebook pages of many politicians coming up and the number of such pages is on rise. It provides direct accessibility of high offices to common man. But its power in full flow is yet to be harnessed by governments.
3. It has shown its prowess recently during campaigns like “Egyptian Revolution” and “India Against Corruption”. It helps to spread cognitive dissonance by connecting thought leaders and activists to ordinary citizens. It is an effective to quickly spread the message to masses.  Traditional Media both print and electronic are also integrating it. Now to remain updated we just need to create our list of channels we want to follow.

But this genie can be destructive too if falls into wrong hands, but that price is associated with every technological advance. Isn’t it ? Nuclear fuel is call of the day and it can fulfill our ever growing energy needs. Irony is threat that same nuclear weapons falling into wrong hands in some countries is sends shiver down the spine of whole world.  Recent disturbances in Bangalore, Silicon Valley of India, are example of such incitement. Another hoax that surfaced was about Samsung paying $1.05 bn fine to Apple in nickels. Authenticity has been a major challenge. So its upon individual users to decide what to follow and spread.  Censorships of social media is being talked about. But I think it needs a thorough and greater exercise to establish a process to identify the processes and lay regulations.

* http://en.wikipedia.org/wiki/List_of_social_networking_websites

Follow

Get every new post delivered to your Inbox.