×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

OWASP Tutorial on Advanced SQL injection
3

OWASP Tutorial on Advanced SQL injection

OWASP Tutorial on Advanced SQL injection

(OP)
I've recently discovered that the Open Source Web Application Security (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/details.aspx?FamilyId=428D5727-43AB-4F24-90B7-A94784AF71A4&displaylang=en

John
 

RE: OWASP Tutorial on Advanced SQL injection

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.

RE: OWASP Tutorial on Advanced SQL injection

John - thanks for the link.

Dr Tree

RE: OWASP Tutorial on Advanced SQL injection

Two words:

Stored procedure.

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

RE: OWASP Tutorial on Advanced SQL injection


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: How to get the best answers first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

RE: OWASP Tutorial on Advanced SQL injection

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:

CODE

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

Tell me how newbies like me should fix that.
 
  
   

RE: OWASP Tutorial on Advanced SQL injection

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.

 

RE: OWASP Tutorial on Advanced SQL injection

(OP)
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

CODE

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:

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 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
 

RE: OWASP Tutorial on Advanced SQL injection

John,

I'll have to digest your post.

Mike

(see my next post)

RE: OWASP Tutorial on Advanced SQL injection

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):

CODE

<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:

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="(http://|\.js|[\\{}':%()])"
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):

CODE

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

CODE

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


The new form_ar_test.asp does this:

CODE

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.

 

RE: OWASP Tutorial on Advanced SQL injection

Looks good to me.

C

RE: OWASP Tutorial on Advanced SQL injection

(OP)
Did the new record go into the table?

John
 

RE: OWASP Tutorial on Advanced SQL injection

(OP)
One more thing - you are using a relative path.
I'd check that you can't go to (for example) www.example.com/db/registration_test.mdb and download the entire database.

John
 

RE: OWASP Tutorial on Advanced SQL injection

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

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

RE: OWASP Tutorial on Advanced SQL injection

(OP)
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 www.example.com, www.example.org and 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
 

RE: OWASP Tutorial on Advanced SQL injection

Quote:

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.   

RE: OWASP Tutorial on Advanced SQL injection

Quote:

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?


Quote:

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.  



  

RE: OWASP Tutorial on Advanced SQL injection

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close