Coding techniques for protecting against Sql injection

That link has a ton of information on it, not all of it useful.

If you're using ASP.Net then you really only need to do two things when dealing with Sql Injections and XSS attacks:
1. Parametrized SQL queries (stored procs would be the best)
2. Html Encoding. For example, transforming <html> into &lt;html&gt;.

#1 prevents SQL injections (better protection, again, is using stored procs and never using inline SQL. Stored procs are more efficient anyway).
#2 prevents XSS (i.e. JavaScript injections).

All of these black lists, quote replacements, etc will drive you mad. What if another developer has to work on your site and they forget (or don't know) to run Request.Form["Text"] into a sanitizing method? Well, then you're screwed unless you use stored procs, parameters, and a good DAL architecture that automatically encodes Html (I can post up an example if necessary).
antic said:
In that example, you not only have to escape any quotes, you have to escape square brackets [] as well, because your SQL is dynamically specifying a db object (the field name).
Click to expand...
I would like to think there are better database designs that can prevent this. Is there an instance in which you need to dynamically specify a DB Object outside of a stored proc? I would like to think a good Data Access Layer would abstract any database fields and stored procs would abstract any database objects.
 
KrisSiegel said:
I would like to think there are better database designs that can prevent this. Is there an instance in which you need to dynamically specify a DB Object outside of a stored proc? I would like to think a good Data Access Layer would abstract any database fields and stored procs would abstract any database objects.
Click to expand...

The certainly are. But the fact is people code in all sorts of ways. As I mentioned, even phpBB - a "world-class" app - uses dynamic SQL all over the place, and indeed you have to remember to use the sanitising function all the time. Here's a snippet from viewforum.php, in phpBB version 3:
Code: 
if ($user->data['is_registered'])
{
	$sql_from .= ' LEFT JOIN ' . FORUMS_WATCH_TABLE . ' fw ON (fw.forum_id = f.forum_id AND fw.user_id = ' . $user->data['user_id'] . ')';
	$lastread_select .= ', fw.notify_status';
}

$sql = "SELECT f.* $lastread_select
	FROM $sql_from
	WHERE f.forum_id = $forum_id";
$result = $db->sql_query($sql);
$forum_data = $db->sql_fetchrow($result);
$db->sql_freeresult($result);

I can see dynamic field names and dynamic table names, using variables we must assume have been previously sanitised. Apparently they're not about to implement a DAL anytime soon. For people who can't afford to rewrite their app, which is most people (particularly open source projects), these pointers can help to patch the holes, which is just as essential as good programming from the start.
 
I'm not so sure I'd refer to phpBB as a "world-class" app due to all of the security issues they've had over the years though I have heard the new version is much nicer. It's unfortunate they haven't implemented a better data access layer (or one at all I should say) if that's code from their latest version as I was under the impression it was almost a complete re-write. I wonder why they avoided using parameters in MySQL?

I can understand the updating of legacy code; I have had to work on quite a few legacy web applications so I agree with you 100% percent. My post was to help those who may be working on an application now. I'm surprised how much dynamic and unparameterized SQL I've seen in .Net applications especially since the better programming practice of stored procs and parametrized queries has been around since .Net began.

Though, I would like to say that I don't think dynamic referencing of tables and other database objects is a very good design but I understand why phpBB did it (since they allow custom table prefixes) though I'd like to think a MS SQL implementation would change the Schema rather than a table prefix.
 
KrisSiegel said:
I'm not so sure I'd refer to phpBB as a "world-class" app due to all of the security issues they've had over the years though I have heard the new version is much nicer.
Click to expand...

Could say the same for Windows.. except the bit about the new version being nicer. :)

KrisSiegel said:
I'm surprised how much dynamic and unparameterized SQL I've seen in .Net applications especially since the better programming practice of stored procs and parametrized queries has been around since .Net began.
Click to expand...

Even more surprising, since many ASPers moved to ASP.NET and parametrised queries were readily available in classic ADO. Only projects with a lot of time and money behind them have a chance of being well written well first time round.

In most cases the boss wants it yesterday, requirements change, new features are needed, and expediency is the only way. Takes a very good developer to keep things in decent shape through the growth of a big project. I don't believe inline SQL is "evil" - sometimes it's preferable - but it must be managed properly.
 
The majority of times we access a DB is to read data. So an obvious fix was to have 2 DB users, one with Read-Only that I use most of the time and the 2nd with Update access.

Ok, its not perfect and when I do then use the Update user, I implement a lot of checks but it puts up an easy effective barrier most of the time.
 
try to use the stored procedures of sql so that sql injection is not possible in you website
and also try to avoid get methods in the website
Trying to block this attack using a javascript wont work for you
because java scripts can be easily disabled on the client end.
 
You must log in or register to reply here.
Back
Top