Preventing SQL Injection in SQL Server TEXT fields using Classic ASP

Question

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

Remus Rusanu · Accepted Answer · 2010-08-05 22:36:42Z

9

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...)

answered Aug 5, 2010 at 22:36

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Caveatrob Over a year ago

Good points. I'm more concerned about the invocation point since i've secured the procedure inside T-SQL. Will have to find a good resource on filtering out XSS attempts. Any ideas?

All Blond Over a year ago

@Caveatrob Use regular expression to verify if certain elements is not in your text.For example limit user to use of "<",">", better yet just do Encoding on submit and decoding on retrieve, that will illuminate any possibility of SQL injection and provide security for the content.

Collectives™ on Stack Overflow

Preventing SQL Injection in SQL Server TEXT fields using Classic ASP

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related