SQL Injection - Best practices

A buddy of mine has recentnly been transferred to the web design and coding part of the company I work at (I took over his old job). He was watching the Code Room and it piqued his curiosity:  He tried that SQL injection on his own site and apparently it's prone.  Can anyone give me some pointers to forward to him regarding web design best practices to prevent SQL injection?

Simple: never concatenate strings to create SQL statements. Always use parameters.

Single quotes are killer.  Filter those out.

That isn't the only way to do them, though.

Another thing to watch out for is when someone puts raw HTML into something that will be shown to someone else at some point.  A really simple quiz web app would dump whatever you wrote back to the owner at some point.  So I put "</body>" into it.  I mean, I uh, I conjectured that would be possible.

Sven Groot wrote:



Simple: never concatenate strings to create SQL statements. Always use parameters.

QFT
parameterize parameterize... parameterize.......

btw i met the guy in that vegas vid (forget his name) at  VSlive! in San Francisco.... really cool guy...

also consider the use of Stored procs if possible and only if they will truly help performance....

you can even create a sub to look for things in the user input like "xp_" , "sp_" ,  " or 1=1 --"  and then record the attempts for security purposes

always remember user input == evil

also make sure to create a user with read only privaleges to the db and use that user to query info.... create another limited user to use when writing to the db.... never use the sa account..... ima say it again just in case...... never use the sa account!

I got a bit of mail from SPI Dynamics, offering a whitepaper on SQL Injection.  I posted a link to it at http://blog.opsan.com/archive/2006/06/02/28647.aspx

Here is a post explaining using parameters to protect your server from injection based attacks.

blowdart wrote:



littleguru wrote: Use parameters. It's impossible to inject by using them.

Oh it certainly is. Heh. The problem would come if you were building dynamic sql inside the statement.

For example take a typical search stored procedure, it would split a search string up by spaces, surround each word with % and split them up with an AND between. The only way to then search is to use that in conjunction with sp_executesql.

And if you pass in a typical injection string, oops ....

Which reminds me, how well do C#-Sprocs make Dynamic SQL obsolete? (if at all)

blowdart wrote:

Oh it certainly is. Heh. The problem would come if you were building dynamic sql inside the statement.

Any time you're building a SQL statement from a string, you need to think about injection.  This is true whether you're building it in ASP.Net, building it in Perl, building it in SQL, building it in Javascript, etc., etc., etc.

I program php and on my website I find it best prcatice to protect my variables with the php mysql_real_escape_string(); function, this adds backslashes to certain characters that are effective in SQL injections. I also find it best to restrict wildcards like % from user input, for example my login script restricts users from using non-alphanumeric characters in their usernames and passwords. So I will use a form to post the user input and restrict it like this -

<?php
$user = strip_tags($_POST['user']);
$user = mysql_real_escape_string($user);
$pass = strip_tags($_POST['pass']);
$pass = mysql_real_escape_string($pass);

if (ereg("[^A-Za-z0-9]", $user)){
$error = "Username's can only contain alpha-numeric characters.";
}elseif (!ereg("[^A-Za-z0-9]", $user)){


if (ereg("[^A-Za-z0-9]", $pass)){
$error = "Password's can only contain alpha-numeric characters.";
}elseif (!ereg("[^A-Za-z0-9]", $pass)){

rest of script here.

}else{
$error = "User input error.";
}
}else{
$error = "User input error.";
}

if (isset($error)){
print $error;
}
?>




Give this a shot:
http://search.live.com/macros/wisemx/aspnet/

blowdart wrote:

What an incredibly bad idea. Instead of escaping the SQL properly you instead stop users using strong passwords.

I come accross a lot of websites and services which put restrictions on passwords, like maxlength and only alphabetic characters. Usually it's a surefire way of indicating they don't hash their passwords, so I use a unique PW for those sites.

Simply put use stored procs or an ORM. Validate everything at every step in the relay (each layer) that can modify a particulair entity. Client -> Business Object layer -> Data Access Layer -> to wherever. This is a fundamental to quality software. Specially but not limited to if you need to use query building in your stored proc.

Limiting your mutable footprint may be a valid option. Knowing is most of the battle.

Later you start planning for this issue the more costly the endeavor becomes. As most things in software egineering:)