If you connect to a database with your server side scripts, the most important task you have to do, is to validate the input before sending it to your database. We see things like this over and over again:
This is just begging for trouble. Your site is a stting duck for SQL Injection attacks, whereby users will purposely input invalid data in an attempt to hack in. If you aren't checking the inputs before sending them to the database, eventually someone's coming in.
A better way to write the above query is something like this:
Dim user
user = Trim(Request("user"))
user = Replace(user, "'", "''")
If len(user) < 1 Then
Response.Write "Please Enter your user name"
Exit Sub
End If
sql = "SELECT someFields " &_
"FROM myTable " &_
"WHERE user = '" & user & "' " &_
"ORDER BY someField"
If you are using MS SQL Server, use stored procedures, learn the Command Object syntax, and abandon all other methods to access the database from applications wherever possible.
The problem with the original query is that one can purposely enter some bad things:
z'; delete from myTable--
z' union select name, dbid from master..sysdatabases--
It doesn't matter what databse you use, nor what scripting language you use. If you don't properly validate input server-side, you're asking for trouble. Things to check for:
- if u expect a number, make sure it IS one
- if you expect a string, check that it is greater than zero length, if you know how long it should be, make sure it is that long
- escape single quotes in string input by doubling them up.
Remember to validate all input: this means Post, Get and Cookie input. Even if it is a hidden form element, validate it. A hacker won't use his web browser (for long) to get in, he's going to write a script to post garbage at ya, and see if he can crack in.
I won't go into more detail as there are some good references, and of course a quick search on your favorite SE will pull up more.
It doesn't matter what databse you use, nor what scripting language you use. If you don't properly validate input server-side, you're asking for trouble. Things to check for:
- if u expect a number, make sure it IS one
- if you expect a string, check that it is greater than zero length, if you know how long it should be, make sure it is that long
- escape single quotes in string input by doubling them up.
When you say escape single quotes in input by doubling them up can you explain that for me.
i am storing a textbox in a variable then use the insert value of
Code:
'" & variable & "'
how can i avoid the db error if someone enters something like o'reilly's?
A particular and very common oversight is login scripts that aren't sanitised.
Checkout the basic example below:
Code:
Dim strUsr
Dim strPass
Dim strLogin
strUser = Request.Form("usr")
strPass = Request.Form("pass")
strLogin = "SELECT * FROM tblUsers WHERE usr=" & strUser & " AND pass=" & strPass
From this point it is common to then query the database and check the recordset for a result. If you get a result then it must be OK so let's proceed. If this sounds familiar, then think again. It's dangerous and SQL injections are the reason why.
If I pass:
Code:
Usr: ' OR 1 = 1
Pass: ' OR 1 = 1
to the ASP above then the SQL will produce a result when queried. Hey Presto! I'm in and I haven't got a password.
SQL produced:
Code:
SELECT * FROM tblUsers WHERE usr='' OR 1=1 AND pass='' OR 1=1
The rule is, as stated above, strip quotes from login scripts and always read the database results, don't rely on the fact the recordset object produces a result.
Another point is some databases allow concurrent queries which are seperated by a semicolon so another dangerous injection would be:
Code:
Usr: ';DROP TABLE tblUsers;
This doesn't always have to affect the database as never ending JavaScript alert box loops can also be injected. Harmless when stored but a browser crasher when rendered.
Code:
<script type="text/javascript">
var i;i=1;while(i>0){alert("Stoopid")}i++;
</script>
There are literally hundreds of ways of injecting code into databases for malicious purposes so always screen the users input.
Reply With Quote
Originally Posted by russell
Bookmarks