Preventing Sql Injection in Statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello!

I am trying various ways of preventing the injection. I tried this:

"select count(*) from table where userid like @userid"
and
SqlParameter param0 = new SqlParameter("@userid", SqlDbType.NVarChar,50 );
param0.Value = Session["userid"];
myCommand.Parameters.Add( param0 );

That just didn't work.

I tried sp_executesql, but I found out you can do this (similar to above):

EXECUTE sp_executesql @SQL, @ParmDefinition, @userid = '22'; drop table
tktrans

The other answer I got was that I should write a stored procedure for
everything I do. I have two questions: 1-that will take me forever 2-is that
any safer?

Please advise if there are any other options. Thank you!

-Bahman
 
Bahman said:
Hello!

I am trying various ways of preventing the injection. I tried this:

"select count(*) from table where userid like @userid"
and
SqlParameter param0 = new SqlParameter("@userid", SqlDbType.NVarChar,50 );
param0.Value = Session["userid"];
myCommand.Parameters.Add( param0 );

That just didn't work.

I tried sp_executesql, but I found out you can do this (similar to
above):

EXECUTE sp_executesql @SQL, @ParmDefinition, @userid = '22'; drop table
tktrans

The other answer I got was that I should write a stored procedure for
everything I do. I have two questions: 1-that will take me forever 2-is
that
any safer?

Please advise if there are any other options. Thank you!

-Bahman
Click to expand...

Stored Procedures don't take forever :) They are actually fairly easy to
write and even easier to maintain (all stored procedures for a database are
in the same location and you don't have to re-compile code if you make minor
changes to it). They also stop SQL injection attacks. Safer, yes, will
that take you forever, probably not.

Mythran
 
I'm, not seeing what you're seeing.
When I execute the following code:

Imports System.Data

Imports System.Data.SqlClient

Public Class Form1

Dim cn As SqlConnection

Dim cmd As SqlCommand

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

cn = New SqlConnection("Data Source=xxx;Integrated Security=SSPI;Initial
Catalog=Biblio")

cn.Open()

cmd = New SqlCommand("SELECT au_id, author FROM authors WHERE Author LIKE
@idwanted + '%' ", cn)

With cmd

..Parameters.Add("@idWanted", SqlDbType.VarChar)

..Parameters(0).Value = "H ;drop table fred"

Dim dr As SqlDataReader = .ExecuteReader

End With

End Sub

End Class

ADO.NET executes this: (according to the profiler):

exec sp_executesql N'SELECT au_id, author FROM authors WHERE Author LIKE
@idwanted + ''%'' ',

N'@idWanted varchar(18)',@idWanted='H ;drop table dbo.fred'


Note that the Parameter is set to "H ; drop table dbo.fred"... it's not
simply tacked on to the SQL. Since the string (the complete string) is used
as the query Parameter and framed in single quotes it does not "execute" the
drop table. If you try to stick in another single quote, ADO.NET's Command
processor knows to double it up so the server executes this:

exec sp_executesql N'SELECT au_id, author FROM authors WHERE Author LIKE
@idwanted + ''%'' ',N'@idWanted varchar(19)',@idWanted='H '';drop table
fred'

The point? Nope, you don't need stored procedures for everything. You DO
need Parameter-based Commands though. You're right to be concerned. "All
input is guilty until proven innocent." You heard it here first.

It's also not that hard to create a Parameter... you're killing a goldfish
with dynamite.

sqlCommand cmd;
cmd.Parameters.Add("@p1").Value = "test";

Don't worry about the datatype... it can take care of itself (even in C#).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


Bahman said:
Hello!

I am trying various ways of preventing the injection. I tried this:

"select count(*) from table where userid like @userid"
and
SqlParameter param0 = new SqlParameter("@userid", SqlDbType.NVarChar,50 );
param0.Value = Session["userid"];
myCommand.Parameters.Add( param0 );

That just didn't work.

I tried sp_executesql, but I found out you can do this (similar to
above):

EXECUTE sp_executesql @SQL, @ParmDefinition, @userid = '22'; drop table
tktrans

The other answer I got was that I should write a stored procedure for
everything I do. I have two questions: 1-that will take me forever 2-is
that
any safer?

Please advise if there are any other options. Thank you!

-Bahman
Click to expand...
 
Hi Bahman,

What your code I do not see any problems with the injection. You do not
concatenate any string and passing userid as a parameter and this is a safe
way. What makes you believe that you have an injection issue here? You
cannot inject another SQL statement if you pass it as a parameter.
 
Don't worry about the datatype... it can take care of itself (even in C#).
Click to expand...

Bill,

On the contrary, he SHOULD take care of the type.. What if no one is paying
attention to types of data they pass? How people can be good programmers
without understanding data types?
BTW, if his user id is VARCHAR and his parameter is SqlDbType.NVarChar, and
he has millions of records, his query may be 100 times slower if not more.
And in this case it seems that his userid is int not (n)varchar so he should
convert it like Convert.ToInt32(Session["userid"]) before he sends query to
db.. not mentioning that the amount of data to be sent is smaller.

Peter
 
Back
Top