FAQ - Frequently Asked Questions

Re: Avoid SQL Injection attacks

slavik118 — Mon, 05 Oct 2009 11:29:16 GMT

As for me, it is very cut and dry with the matter - migration to LINQ to SQL clears up the problem: http://msdn.microsoft.com/en-us/library/bb386929.aspx

Re: Avoid SQL Injection attacks

Chandra Prakash Andani — Thu, 01 Oct 2009 17:04:52 GMT

very simple and good post!

it will be helpful for many developers

Re: Avoid SQL Injection attacks

k.srinivas81 — Thu, 01 Oct 2009 07:31:41 GMT

The main weak point for SQL injection attack is writing in-line SQL or building the statement in the Stored Procedure dynamically using string concatenations. To avaoid SQL injection attacks use Stored Procedures and pass the only values.

Re: Avoid SQL Injection attacks

Arif Sheikh — Tue, 21 Jul 2009 07:48:44 GMT

I agree with Manas. I use the Helper SQL myself. I had several sql injection attacks in the last few months to drop the database or change the data, but all of them were balked.

Re: Avoid SQL Injection attacks

TATWORTH — Sun, 12 Jul 2009 21:32:03 GMT

Thank you for posting the link.

Re: Avoid SQL Injection attacks

Naom — Sun, 12 Jul 2009 07:43:34 GMT

Not sure, if this great article was already referenced in this thread

The Curse and Blessings of Dynamic SQL

Re: Avoid SQL Injection attacks

hminaya — Fri, 03 Jul 2009 15:40:29 GMT

This kind of attack has been around for a long time, and I don't think it will go away any time soon...

Re: Avoid SQL Injection attacks

TATWORTH — Thu, 02 Jul 2009 19:30:13 GMT

nothingisnecessary:
Is there a way (a database setting or data provider option) to disable or disallow multiple statements being executed by Mssql?

A very good question! Should such an option exist it would be a further barrier to malicious input!

As it is a specialist SQL question it would be best asked at:

SQL Server Team http://www.sqlteam.com/forums/
SQL Server Central http://www.sqlservercentral.com/
SQL Server at MSDN http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=19&SiteID=1

Re: Avoid SQL Injection attacks

nothingisnecessary — Thu, 02 Jul 2009 17:30:42 GMT

A purely academic question to SQL Server gurus:

Is there a way (a database setting or data provider option) to disable or disallow multiple statements being executed by Mssql?

Please do me a favor and spare me any answers of "use stored procedures," "sanitize your input," "run as an unprivileged user," etc. (Y'all have already made several good points in this thread, and I'm aware of the best practices, but like I said, this is just a purely academic question that begs a yes or no answer, not philosophy.)

The question is: can you disable multiple statements in SQL Server, and how?

For example, as a way to prevent the most fun and trivial of sql injection shown in this cartoon:

http://xkcd.com/327/

Thanks!

Re: Avoid SQL Injection attacks

shados — Wed, 01 Jul 2009 20:15:17 GMT

As i mentionned earlier in this thread, if we're talking about database security in general, yes. If we're talking about SQL injection (which is what this thread is about), the -only- thing you need is to use parameterized queries (stored procedures are a type of parameterized queries, but do not enhance security beyond the normal kind) and to avoid dynamic sql within stored procedures.

Once you do that, it is impossible to use sql injection against you, even if you don't cast datatypes and your app is running as domain administrator (still err...don't do that for other reasons, obviously!).

Other exploits could be used, of course, so these best practices are indeed useful, but i feel its important to make the difference between sql injections and general database best practices, as too much confusion over the subject hurts more than help... See the same pattern about XSS attacks, where programmers can't properly defend themselves because they're confused and mixed up over a bunch of different exploits.

Re: Avoid SQL Injection attacks

TATWORTH — Wed, 01 Jul 2009 19:17:36 GMT

>Good post but best way is to avoid queries in code

Moving embedded TSQL into stored procedures is only part of the solution as defence must in depth, therefore also:

A minimum priviledge SQL account
All data cast to the correct data type

Re: Avoid SQL Injection attacks

kthummala — Wed, 01 Jul 2009 10:21:48 GMT

Good post but best way is to avoid queries in code (Best method is write procedures )

Re: Avoid SQL Injection attacks

TATWORTH — Mon, 29 Jun 2009 17:51:40 GMT

In the post at http://www.unixwiz.net/techtips/sql-injection.html, there is a very important point:

"Instead, rather than "remove known bad data", it's better to "remove everything but known good data": this distinction is crucial"

In otherwords use a White List of what you know to be valid and not a black list.

Re: Avoid SQL Injection attacks

omalbose — Sat, 28 Feb 2009 16:41:53 GMT

Thank you boss it is nice article about the sql injection. Following link also will be useful for the programmers to familier with the Different sql injection methods employed to break into DB. When we are aware of such threats we can prepare better prevention methods

http://www.unixwiz.net/techtips/sql-injection.html

Hope this would help others.

Re: Avoid SQL Injection attacks

shados — Tue, 24 Feb 2009 15:49:29 GMT

If the query can be flattened in a way that the optimizer can easily figure out that the conditions will always return true (and it can do that fairly aggressively, thanks to statistics usage, among other things), performance will be similar.

That said, my main point was mainly that when using dynamic sql through parameterized queries, to try, while staying reasonable, to keep the queries mostly static and to use parameters as much as possible, to avoid clogging the query plan cache.