If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Validate Your Input!

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.

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?

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.