SQL injection and parameterized queries

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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL injection and parameterized queries

Author

Topic

dcarva
Posting Yak Master

140 Posts

Posted - 2005-09-11 : 10:58:41

Hello,

All of my sql calls are parameterized and call stored procs. I am also careful to make sure that the account calling the stored proc does not have powerful access (like sa). With that said, I read somewhere that parameterized queries will prevent sql injection. Therefore, do I still need to validate input against sql injection if I used parameterized queries? In other words, make sure that the user does not enter "select" or ";" or "xp_" or "insert" in my forms?

Here is a sample of how I make calls:

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open OBJ_CONN
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
.ActiveConnection = objConn
.CommandType = adCmdStoredProc
.CommandText = "spLogin"
.Parameters(1) = Request.Form("userid")
.Parameters(2) = Request.Form("password")
.Execute
nRet = .Parameters(0)
End With

Thanks!!

Kristen
Test

22859 Posts

Posted - 2005-09-11 : 13:20:19

Provided your SProcs do not do dynamic SQL you should be OK

i.e. if you have some code in your SProcs along the lines of

SELECT @strSQL = 'SELECT * FROM MyTable WHERE ' + @Parameter1
EXEC (@strSQL)

then that would be vulnerable to SQL Injection

Another way to put it is that if the "user" that you application uses to log on to SQL has NO permissions on the underlying tables (i.e. ONLY has Execute permissions on the SProcs) then you should be fine.

Kristen

dcarva
Posting Yak Master

140 Posts

Posted - 2005-09-11 : 15:35:52

Thanks! Looks like my code is ok then.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources