slavicek.net
 Domů
Cestování
Management znalostí
Závěrečné práce
Různé nápady
Luštění hlavolamu
Sql injekce
e-mail:
posl. změna 4.4. 2007
čeština  |   english
 Útoky způsobované SQL injekcemi a jejich prevence

Dnes je tomu pět let, co jsem vyvíjel webové stránky pomocí technologie ASP, a zamýšlel jsem se nad co možná nejlepší ochranou kódu proti tzv. SQL injection. Nástroj popsaný níže vznikl jako vedlejší produkt této snahy. Domnívám se že nyní, dávno po zlaté éře ASP, nyní kdy každá profesionální webová stránka využívá (nebo by alespoň měla) výhod vícevrstvé architektury, uložených procedur nebo ORM mapování, nyní by mohlo být docela zajímavé publikovat tento nástroj, abychom si uvědomili, jak málo informace stačí k proniknutí do střeženého systému.

Zbytek článku existuje pouze v anglickém jazyce, omlouvám se.

  
Upozornění     
> 
   Jediným účelem této aplikace bylo studium slabin, které mohou vzniknout při vývoji softwaru, za účelem budoucí efektivnější eliminace těchto slabin a obrany proti případným útokům na vyvíjený software.

Autor se zříká zodpovědnosti za vzniklé škody nebo protizákonné jednání vzniklé v souladu s užitím publikované aplikace k jinému účelu než uvedenému výše. 		 
  
Definition     
> 
   SQL injection is a security vulnerability that occurs in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another. [Wikipedia]

Lots of people know the simplest possible use of Sql injection. When a web page requires a username and password, one can always try to type something like this to the both fields:

' or ''='


hoping that the application doesn’t handle apostrophes properly, so the resulting SQL would look as follows:

select ID from Users where (UserName = '' or ''='') and (Password = '' or ''='')


As the result, one would be logged in as the first user from the Users table.

Developers are often afraid of the DROP table statement occurring within an injected code, however this is not as easy to achieve for an attacker as it might look to. The following example works for sure in Query Analyzer, but not so surely as a part of a malicious injected code:

select ID from Users where (UserName = '' or ''='') and (Password = '' or ''='')
drop table Users
go


Let’s focus on Sql Injection in connection with retrieving confidential information from a server. Of course, the following code is not functional, because the retrieved data would not be delivered from the web server to client’s browser:

select ID from Users where (UserName = '' or ''='') and (Password = '' or ''='')
select UserName, Password from Users
go


However, the next chapter will convince you that even something like this is possible by means of Sql Injection. 		 
  
Scenario     
> 
   See the picture below for the five steps a typical Sql Injection attack may involve.

File steps
Fig. 1 - Five steps involved in the Sql Injection attack described in this article.

Finding a location on the web site an attack can be made through

This can be done by manually, or a special tool may be developed to help the intruder to find such locations. Detail specification of such a tool is not part of this article, though. In a nutshell, each web site can be considered as a graph, pages as leaves and HTML links as connections among the leaves. The tool would scan all pages hierarchically, searching for HTML controls on pages, trying to pass improper (non-integer) parameters in post-backs. If this produces a database error, a weak point has been found.

The paradox is that the tool can serve both to quality engineers to test the product before the release, and to hackers to find undetected weak points after release.

Please notice that Select html elements are much more likely to fail our test rather than text fields. This is because of its easier slipping through the testing phase. No tester would try to replace a drop-down list with a text box in an HTML code (unless explicitly asked).

Also, it’s more likely to find such a place in parts of a web site that have been additionally developed/modified, so the developers had given up their intensions to make a “steel-sheet covered web site” a long time ago.

Another requirement for the described approach is that DB errors are exposed on the web page. All we need are at least two distinct DB errors (I’ll find out why soon).


Retrieving the list of tables

Let’s assume an attacker has found a week point on the web site you and is about to attack it. Alternatively, a ready-to-be-hacked ASP.NET application can be downloaded from the following location:



The application requires .NET Framework 2.0, MS SQL 2000/2005 and the AdventureWorks sample database installed on your machine. The application contains security vulnerabilities to enable the tool to be successful even in the ASP.NET environment. Anyway, in times of ASP, it was not exceptional to find pages like this one.

  • If you don't have MS SQL server on your computer, MS SQL 2005 Express Edition can be downloaded for free.
  • If the AdventureWorks database is missing on your DB server, it's available for download from SQL Server 2005 Samples and Sample Databases.
  • Download the archive above and put the unwrapped folder on your machine. Map a virtual web site (virtual directory) to it.
  • Modify the connection string in the web.config file appropriately to be able to connect the AdventureWorks database from the ASP.NET application.
  • Eventualy, you should be able to see a simple ASP.NET page in the specific location (http://localhost/SqlInjectionDetector/Test.aspx, for example).
Next, download and execute the following HTA application:



If the location of the testing script differs from http://localhost/SqlInjectionDetector/Test.aspx, modify line 35 of the script appropriately.

You'll see that the application will retrieve the list of AdventureWorks tables in couple of seconds.
Click to Enlarge
Fig. 2 - Retrieving the list of tables.
How does the script work? Since the malicious SQL code can result in several error messages, the type of message itself can represent a piece of information. To obtain single bit information whether it’s day or night, for example, we have to inject the following code:

…/Test.aspx?id= … (case when hour(getDate()) > 20 or hour(getDate()) < 6 then 1/0 else 'X' end) …


Now, the result during the day will be

Division by Zero


Whereas as the Sun went down, one might obtain an error like following:

Error converting data type varchar to numeric


I just hear you wondering: “That’s all clear to me, but information on a username and password an intruder tries to find, for example, obviously needs more than single bit to transfer”. Well, you are quite right. But remember there exists something like a serial transmission. He can transfer the information bit by bit, within more than just one post-back.

For retrieving one letter from English alphabet, one has to post x queries, where 26 <= 2x , i.e. x=5.

We can inquire: „Is the first letter of the username I want to know lower than M?” If the answer is YES, we can continue with the lower half of the character set: “Is the first letter of the username I want to know higher than F?”, and so on.

The rest is just building a proper query to the sysobjects table.


Retrieving the list of columns for the table of interest

Click to Enlarge
Fig. 3 - Retrieving the list of columns for the table of interest.
The next step for an attacker is to choose which table from the list looks promising. In our case, we expect it to be the Individual table.



The involved algorithm is pretty much the same as in the step above. We are looking for names of all columns from the syscolumns system table, assigned to the object named as "Individual".


Retrieving values for the specified column and row

Click to Enlarge
Fig. 4 - Retrieving values for the specified column and row.
The last step we are focusing on is about retrieving values from the specified table, column, and row. In our example, the piece of information to obtain is located in the Individual table, LastName column and the first row.



You can check your AdventureWorks database to see that it really contains the string “YANG” at the specified location.
 
  
Defence     
> 
   In the beginnings of ASP, the only resolution was to build SQL queries in the following way:

"select * from MyTable where id = " & cint( Request.Querystring("id") )

or
"select * from MyTable where name = " & Replace( Request.Querystring("name"), "'", "''")

respectively.

Fortunately, the things turned to be much easier with .NET:
  • We should always separate the user interface and the business logic. Sometimes, multi-tier architecture plays its good part as well. Since the parameters retrieved during post backs are handled as strong typed all the way down through different layers (user interface -< business layer -< database layer), the risk of a possible sql injection is quite insignificant.
  • For the approach described in this article, an attacker needs DB error messages being exposed, and it’s quite easy to configure custom error pages in ASP.NET. But alas! There would be no use of custom error pages after a slight modification of the tool. Remember we can create a query resulting in {no error | some error }, which is a single bit piece of information, too.
  • We can narrow privileges granted to the DB user. The Individual table can be accessible only for some DB users, for example. But remember that sysobjects and syscolumns tables are accessible every time, even with not explicit permissions granted.
  • Password should never be stored as a clear text in a Users DB table, but MD5 hashed (the Individual table from AdventureWorks is a good example). It doesn’t give us the 100% security, since the attacker can still use a brutal force method to puzzle the password out. On the other hand, it protects the data not only for the case of Sql Injection, but against all ways passwords can be stolen from a database.
  • When using custom error pages, a message like “your IP address is A.B.C.D, the error has been logged, and sent to the administrator.” might be helpful, too. Probably even more than actual logging or sending whatever wherever.