OWASP Tutorial on Advanced SQL injection 3

[jrbarnett]

I've recently discovered that the Open Source Web Application Security (

http://www.owasp.org)

community website has a Powerpoint presentation on advanced SQL injection techniques. Examples for SQL Server, Oracle and MySQL are all included.

From

http://www.owasp.org/

click Presentations on the navigation menu, then scroll down to Advanced SQL Injection by Victor Chapela.

I hope I need not remind you that such techniques should only be used against systems for which you have explicit permission to access or test the security of.

If you don't have Microsoft Powerpoint, the free Powerpoint viewer can be downloaded from

http://www.microsoft.com/downloads/...27-43AB-4F24-90B7-A94784AF71A4&displaylang=en

John

 

[dilettante]

Nice overview of the topic. Sadly exactly those who need it probably won't take the time to understand it and properly avoid it. Still, getting the word out there is half the battle... even though it's 2008 now and this is pretty old-hat stuff.

The real culprit is all of the bad information out in the wild using techniques vulnerable to precisely this attack vector. What bugs me most is that many book authors and web posters disclaim such dynamic SQL as "an example, don't do this in a real application" - yet people just monkey-copy it anyway.


The presentation was a bit light on practical ways to arm yourself against SQL injection though. The major theme being to avoid dynamically constructing SQL statements at all.

 

[jrbarnett]

To supplement this, how to lock down SQL Server 2005:

http://duartes.org/gustavo/articles/Lock-Down-SQL-Server-2005.aspx

John

 

[drtree]

John - thanks for the link.

Dr Tree

 

[LNBruno]

Two words:

Stored procedure.

< M!ke >
[small]Where are we going and why am I in this handbasket?[/small]

 

[johnwm]

myearwood
The link you reference seems to be for a specific combimation of server and db. It's worth noting that MSSQL is not the only SQL and that ASP.NET is a relatively low-popularity mechanism for websites to access a database. jrbarnett's link provides valuable general information that covers other more popular web techniques as well. LNBruno provides a very succinct step in the right direction, although it lacks some of the rigour of the first reference.

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[BigRed1212]

My problem with this PPT presentation is the same one I have with most if not all of them. I understand the issue. I get that my database is vulnerable. I want to fix it.

The presentation say I should just "call stored procedures through a parameterized API" and "validate all input through generic routines". I have no idea how to do a parameterized query in classic ASP hitting Access.

When somebody makes me a PPT or even a post here that tells me how, then we'll have something.

I get that I shouldn't do:

sql = "SELECT * FROM users WHERE login = '" & formusr & 
"' AND password = '" & formpwd & "'"

Tell me how newbies like me should fix that.

 

[Craig0201]

BigRed,

Access can use parameterised queries.

See

http://www.4guysfromrolla.com/webtech/021799-1.shtml

for examples of how to call through ASP.

C

 

[BigRed1212]

C,

I'll have to try to make sense of that, translate it to something I can follow, and then test it to see if it works.

I'll repost when I can't get it to work and you guys can give me hints.

 

[jrbarnett]

Please note - I'm not an ASP developer, I'm a VB/VBA developer - as a result, this has not been tested at all. However, I hope it should give you the general principles of what is needed:

ASP code for your web page

Dim oConnection ' ADODB.Connection object to connect to database

Dim oRS ' ADODB.Recordset to host results of data query

Dim sQuery ' String data for SQL query

' Connect to a Microsoft Access Database from ASP using DSN-less OLEDB  connection 

Set oConnection = Server.CreateObject("ADODB.Connection")
oConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & 
Server.MapPath("d:\data\yourdb.mdb") & ";UID=Admin;PWD="

sQuery = "qryValidateUser" ' name of query from Access front end

' Add parameters to the query as comma separated items
sQuery = sQuery & "'" & Username & "','" & Password & "'"

Set oRS = Server.CreateObject("ADODB.Recordset")

oRS.Open sQuery, oConnection

' now validate the query

If oRS!Total > 0 Then
  ' Correct password
Else
  ' Incorrect Password
End If

' Close the connection

oConnection.Close
Set oConnection = Nothing

Create the query qryValidateUser with the following SQL code:

SELECT count (*) As Total
FROM Users
WHERE UserID = [EnterUserID] And Password = [EnterPassword]

When you run this query manually through Access, and enter a valid user id/password combination, you get a 1, if not, you get 0. That is your overall status

This way:
1. You aren't retrieving the password as a valid from the db - its just retrieving 0 if there is no valid username/password combination, or 1 if there is.

2. If you encrypt the passwords, then the entered password needs to be scrambled in the same way before validating.

3. Access query strings can be found from

http://www.connectionstrings.com

if you need help to customise it to your own needs.

4. Using Server.MapPath means you can store the data outside the web root, which is good for security (ie your users can't go to

http://www.example.com/somedatabase.mdb

and download the entire list of users and passwords.

I'd be interested to hear how you get on.

John

 

[BigRed1212]

John,

I'll have to digest your post.

Mike

(see my next post)

 

[BigRed1212]

Ok, Here we go:

I have a database of registrations for a local soccer league called registered_test with a table called registered.

I have a standard registration form that collects player information (just using the first three fields for this):

<form id="registration_form" name="registration_form" method="post" action="form_ar_test.asp">

<fieldset>
    <legend>Personal information</legend>
      <label for="fname"> First name:</label>
      <input name="fname" id="fname" type="text" />
 
      <label for="lname"> Last name:</label>
      <input name="lname" id="lname" type="text" />
 
      <label for="stradd">Address:</label>
      <input id="stradd" name="stradd" type="text" />
</fieldset>

</form>

The from hits a page (form_ar_test.asp) with this code:

<% Dim fname, lname, stradd, data_source, con, regex
fname=Request.Form("fname")
lname=Request.Form("lname")
stradd=Request.Form("stradd")

data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &_
Server.MapPath("db/registration_test.mdb")

Function DeleteChars(str)
Set regex = New RegExp
regex.pattern="([URL unfurl="true"]http://|\.js|[/URL][\\{}':%()])"
regex.Global = True
DeleteChars = regex.Replace(str,"")
Set regex = Nothing
End Function

What I had been doing was building a concatenated insert statement with a little cleaning (stole the cleaning code from this forum):

sql_insert = "INSERT into registered (fname, lname, stradd) values ('" & DeleteChars(fname) & "', '" & DeleteChars(lname) & "', '" & DeleteChars(stradd) & "')"

Set con = Server.CreateObject("ADODB.Connection")
con.Mode = 3
con.Open data_source
con.Execute sql_insert
con.Close

This was/is, as I understand things, "A Bad Thing and Maybe Vulnerable to SQL Injection."

For this experiment in learning about parameterized queries, WITHIN ACCESS, I made a query that looked like this and saved it as insert_player2

INSERT INTO registered ( fname, lname, stradd )
VALUES ([fname], [lname], [stradd]);

The new form_ar_test.asp does this:

Set con = Server.CreateObject("ADODB.Connection")
con.Mode = 3
con.Open data_source
con.insert_player2 fname, lname, stradd
con.Close

That is certainly shorter and easier to write.

Have I done it or am I still vulnerable or even worse?

I registered as "Billy; drop table registered;" and nothing happened.

 

[Craig0201]

Looks good to me.

C

 

[jrbarnett]

Did the new record go into the table?

John

 

[jrbarnett]

One more thing - you are using a relative path.
I'd check that you can't go to (for example)

http://www.example.com/db/registration_test.mdb

and download the entire database.

John

 

[LNBruno]

Hey, John - I got a 404 clicking on that link.

< M!ke >
[small]Where are we going and why am I in this handbasket?[/small]

 

[jrbarnett]

As I said - that is an example URL. BigRed1212 uses "db/registration_test.mdb" as the address for the database connection. This is of course relative to the root of the web page, which suggests to me that this is still vulnerable to an attacker being able to download it (re read my comments earlier about storing the database outside the web root).

The domains

http://www.example.com,

http://www.example.org

and

http://www.example.net

are reserved for use in testing and sample documentation as per section 3 of RFC 2606. Anything sent to them is just ignored.
As far as TT itself is concerned, therefore, they make ideal domains for sample web page links, email addresses etc since you can guarantee there won't be any production systems running on there. Go and have a look at the root of one of those websites to see what I mean.

John

 

[BigRed1212]

Did the new record go into the table?

Yes. It went in as "Billy; drop table registered, Smith, 123 AnyStreet".

If I sign up as <blink>Billy</blink> Smith, yes it will blink when I display it back on a Web page so maybe I need to figure out how to strip that out.

I may try:

<code>
con.Open data_source
con.insert_player2 DeleteChars(fname), DeleteChars(lname), DeleteChars(stradd)
con.Close
</code>

and modify my cleaning code to include "<" and ">".

Or maybe I need to learn about server.htmlencode.

 

[BigRed1212]

One, use parameterization to get the value into the table.

As I tried to indicate, I hope that is what the above does. Does it?

scrub out html tags, but don't bother scrubbing out SQL stuff.

I guess I would think if it is .000000000003 seconds more of processing time to check for :,',",%,/, and \ in addition to http, <, and > why not just look for them all? Maybe I'm missing something.

 

[Olaf Doschke]

The point is, scrubbing out some chars is not the real thing for preventing SQL or HTML injection.

You can also prevent HTML injection by htmlencoding input, thus, that the input will be seen as entered. But ideally you want to convert blinking Billy simpkly to Billy, so you would want to erase html tags, not just lowerthan and greaterthan symbols.

Regarding SQL: Parametrization of input rather than concatenating it to an sql string you would for example not need to escape "O'Brian" to "O''Brian" for SQL Server. As a parameter it does not hurt if there are ' within the input. The statement is parsing the parameter name, not it's value. And the value of the parameter is stored as is, so no risk of injection.

Bye, Olaf.