SQL Injections and parameterized SQL

[LtyChan]

This might be a stupid question - but can I be sure to avoid SQL injections
by using parameterized queries? Will SQL Server or ADO.NET kill any attempts
to drop or update data through sql injections (ei '; update ..... ;--), and
can see a log of any attempts somewhere (will SQL Server warn or fail)? Can
I read about what really happens when using parameterized queries
(SQLClient) somewhere?

Thanks

 

[Robert Simpson]

In a parameterized query, the SQL statement with its parameter markers is
prepared, and then the parameters are pushed separately. The values inside
the parameters are not parsed by the SQL query parser, so there's nowhere in
the codepath for an injection to occur.

Robert

 

[Miha Markic]

Hi there,

This might be a stupid question - but can I be sure to avoid SQL
injections by using parameterized queries?

Yes. 100%.

Will SQL Server or ADO.NET kill any attempts

to drop or update data through sql injections (ei '; update ..... ;--),

SQL server will treat all parameter values as data and not as a part of sql
statement.

and

can see a log of any attempts somewhere (will SQL Server warn or fail)?

No. SQL server (or any other database) doesn't care - it is all data from
SQL server perspective.

Can

I read about what really happens when using parameterized queries
(SQLClient) somewhere?

Parameter values are transfered separately from sql statement and are not
part of statement. Thus one can inject code into parameter values - ok, one
can inject code but the code won't be executed...

 

[LtyChan]

Thanks guys,

Parameterized queries it is then (know that Store Procedures is better -
but i like to keep it in .net code)

Thanks

 

[William \(Bill\) Vaughn]

As many have said, SQL injection is virtually impossible when using
parameterized queries. While I discuss this problem in my book I don't show
how to do it. There are sites and sessions that do so those that would want
to carry out data attacks can do so quite easily.
What was the meaning of the comment about SPs and keeping it in .NET?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)