CODEWITHSUNDEEP
sqlsql-serverasp-classicsql-injection
Caveatrob
Caveatrob

Reputation: 13267

Preventing SQL Injection in SQL Server TEXT fields using Classic ASP

I've got code in ASP that puts values into a Text field in SQL Server using parameterized queries. I was wondering if parameterizing is enough, or if I have to search the field for potential commands, replacing single ticks with double ticks,etc. The text fields are essays, so they might have any number of words or characters.

Am I safe?

sSQL="[usp_SaveDocumentGradeCriteria]"
            Set dbCommand = Server.CreateObject("ADODB.Command")    
            Set dbCommand.ActiveConnection = oConn  
            dbCommand.CommandType = adCmdStoredProc 
            dbCommand.Commandtext=sSQL  
            dbCommand.Parameters.Append (dbCommand.CreateParameter("@CriteriaXML", adLongVarChar, adParamInput, len(saveXML), saveXML))
            dbCommand.Parameters.Append (dbCommand.CreateParameter("@Comments", adLongVarChar, adParamInput, len(commentText), commentText))    
            dbCommand.Parameters.Append (dbCommand.CreateParameter("@documentGUID", adGuid, adParamInput, 0, documentGUID)) 
            dbCommand.Parameters.Append (dbCommand.CreateParameter("@graderFYCUserID", adInteger, adParamInput, 0, fycuserid))  
            dbCommand.Parameters.Append (dbCommand.CreateParameter("@graderSequence", adInteger, adParamInput, 0, graderSequence))  
            if trim(grade)<>"" then
                dbCommand.Parameters.Append (dbCommand.CreateParameter("@grade", adInteger, adParamInput, 0, grade))    
            end if


            set oRST=dbCommand.Execute

Upvotes: 1

Views: 1366

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294277

Passing the Text as parameter will eliminate the possibility of SQL injection for the invocation of the stored procedure. However this does not say anything about the stored procedure itself, it can just as well be exposed to SQL injection if it uses dynamic SQL. And even if the stored procedure is safe, you still have to make sure you do not do any cross-site scripting with the content uploaded when you display it to the client.

Is really an end-to-end game on which you have to secure every single step. Using parameters when invoking the procedure is good, but noone can tell if is enough. You have to follow the data all the way untill is displayed back to the client browser (and perhaps continue even after that if is manipulated by JScripts...)

Upvotes: 9

Related Questions