And even IF escaping quotes would work... there's just no point. Using parameterized queries should be done -anyway- since it helps the Query planner to cache and reuse query plans (which gives stored-procedure-like performance even without using em), among other things...so even WITHOUT thinking about security, you should be using it.

And as a neat side effects, it protects from all sql injections (unless you do something stupid like take a parameter from a SP and run execute sql on it...). It also puts the burden on the drivers and database itself, so if T-SQL changes tomorrow and add features that could be exploited, you're still safe, while if you just escaped dangerous words/characters, you'll be vulnerable.

All these little "tricks" like escaping quotes or other char are from a day before these things were common knowledge, or in some languages were often unavailable (its been possible to use parameterized queries since VB4 i think, but few people knew about it... some other languages had it available, but often in modules that weren't always installed. Fairly old versions of PHP had that issue with some crappy web hosting companies, so people learned to escape strings instead, even though it was less than ideal)

Parameterized queries is the first line of defence. Of course, all the other application & database best practices still apply and are required to protect you against other types of exploits and coding mistakes, such as input validation, but first thing first, parameterized queries.

A lot of developer forgot to filter query string parameter. If you filter the query string you avoid some attack.

Ozo:

A lot of developer forgot to filter query string parameter. If you filter the query string you avoid some attack.

Quite correct! Unless the querystring is encrypted, anything passed via a query string has to be treated with just as much caution  as any user input.

It's not necessary to throw out the baby with the bath water.

I contend that much of the reasoning against building dynamic
queries will see any threat wither away if the developer properly
edits her/his data prior to building the query.

Example:  rather than having a text field for date,
               have three:  year, month, day.
               Restrict them all to being positive integers.
               Year must be four digits that you range
               check appropriately.  Month and day
               must be one or two digits with Month
               being from one to twelve and day being
               from one to the number of days in the
               month.  Leap year check February if day
               is greater than 28.  No injection is at all
               possible.

For longer fields, injection can be stopped using
techniques discussed in other posts to this
thread and elsewhere.

As for the argument of stored procedures
versus dynamic queries, once upon a
time stored procedures where considered
to be faster.  With SQL Server advances,
I've read but not verified that the case is now
that dynamic queries are actually faster
because each query gets optimized.

Regards,
Gerry (Lowry)

Dynamic queries are perfectly safe if all parts of them are trusted, and the non-trusted parts are injected as parameters (via prepared statements), no problem with that.

For the Stored Procedure performance thing, since SQL Server 7 (other big name RDBMS like Oracle, DB2, etc, have been doing it for a while too, but I don't know since when), they basically go through the same "pipe". The stored procedure or the dynamic sql goes through the same "parser", to oversimplify things. So the main difference is that when you call a SP, you don't send as much text, and when you call dynamic sql, you can request the bare minimum fields and simplify it. So it "depends". The important points is that SPs are NOT precompiled, and for dynamic sql query plans to be reused, the query has to be the same everytime (so you want to generize it with parameters).

shados wrote, in part, for "dynamic SQL query [execution] plans to be reused,
the query has to be the same every time".

Often the query may be substantially different from one execution to the next.

Example, consider this input search form:

      First Name:      [                ]
      Middle Name:   [                ]
      Last Name:      [                ]
      Street:    [                                                     ]
      City:       [                                                     ]
      Province/State: [                                            ]
      Country:           [                                            ]
      Postal/ZIP:       [             ]
      Phone:     [        ]   [     ]   -    [             ]
      Marital Status:   Single (  )    Married (   )   other (  )
      Sex:   Female (  )    Male (   )   unknown (  )

If the developer uses a single stored procedure with parameters,
she/he must ensure all non-used parameters are treated as "ALL".

However, if the end user wants only "Gerry" and "Lowry" and male,
a dynamic query will have only three SQL data fields to search
and should, I suspect, execute much faster than a more complex
stored procedure with over ten parameters.

regards ... gerry (lowry)

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.

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.

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.

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

>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
  

 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.

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:  

Thanks!

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

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