Parameterized queries in asp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Parameterized queries in asp

Author

Topic

holster100
Starting Member

13 Posts

Posted - 2008-06-25 : 15:14:21

I wonder if anyone can help. My ASP site is always getting SQL injection attack attempts, so I've decided to parameterize my queries in the code:

Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = objConn
'insert your stored procedure or SQL here
cmd.CommandText = "stored_mysp"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("firstrecord", adSmallint, _
adParamInput,50, firstrecord)

The problem is that if (for whatever reason) the "firstrecord" variable is passed, and it's not an integer, it causes a "parameter not supplied" error (which I get via email).

This is often the case during an attempted SQL attack. I know the stored procedure is not being executed, but how do I safely check the parameters and redirect the user away before I even open a connection?

Is there a verify parameter command?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-25 : 15:18:54

Although I can't answer your question, I thought you might be interested in these:

You might find these tools useful: http://weblogs.sqlteam.com/tarad/archive/2008/06/24/Security-Tools-to-help-customers-with-SQL-injection-attacks.aspx

Also check out Jeff's blog about SQL injection attacks: http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

I moved your thread to the ASP forum.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-25 : 15:31:57

Are you assiging the value to the variable firstrecord?
Then why not to check it before you open a connection?

Madhivanan

Failing to plan is Planning to fail

holster100
Starting Member

13 Posts

Posted - 2008-06-25 : 16:30:17

Yes, good idea madhivanan. What would be the best way of checking a varchar parameter? Is there a command prompt to do it?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-25 : 16:35:43

What is the datatype of the variable firstrecord?
If it is smallint/int, you cant obviously assign any varchar data

Madhivanan

Failing to plan is Planning to fail

holster100
Starting Member

13 Posts

Posted - 2008-06-25 : 16:43:03

Sorry, I was talking about another parameter, which is varchar:

Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = objConn
cmd.CommandText = "stored_mysp"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("strinst", adVarChar, _
adParamInput,50, inst)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-25 : 16:46:21

quote:
Originally posted by holster100

Sorry, I was talking about another parameter, which is varchar:

Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = objConn
cmd.CommandText = "stored_mysp"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("strinst", adVarChar, _
adParamInput,50, inst)

So, what do you want to check on inst?

Madhivanan

Failing to plan is Planning to fail

holster100
Starting Member

13 Posts

Posted - 2008-06-25 : 16:49:34

i want to check that inst is a varchar of less than 50 characters, so I can redirect before it goes into the SP and doesn't cause an error.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-25 : 16:57:28

If LEN(inst)<50
--Do task1
--
else
--Do task2
End if

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources