Capturing SQL injections in classic asp-reposting form puts a lot of ticks

Question

I am using SQL Server 2005 with classic ASP and in the form of repost (I am posting back to the same page), I replace each text field as follows:

course = trim(replace(request("course"),"'","''"))\

The problem with this is that with a repeated error, I have to re-process the form several times, the labels that I replace are multiplied.

Is there any other way to safely check string fields without replacing this view?

Answer 1

it is better to use a parameterized query:

dim cmd : set cmd = server.createObject("ADODB.Command")
dim param
dim sql : sql = "INSERT INTO table(course) VALUES (?)"
cmd.ActiveConnection = yourDBconnection
cmd.CommandType = adCmdText

set param = cmd.CreateParameter("course", adVarChar, , 20, request("course"))
cmd.Parameters.Append param

cmd.CommandText = sql
cmd.Execute

so that you are fully protected with SQL injection

Answer 2

Replace 'only for use in sql string. (what you better do with parameterized queries.)

Answer 3

The easiest way is to only escape the SQL when you really paste into the database:

course = trim(request("course"))

Make the SafeSQL function:

function SafeSQL(TempStr)
    SafeSQL = Replace(TempStr,"'","''")
end function

Then when you insert:

"INSERT INTO table(course) VALUES ('" & SafeSQL(course) & "')"

Disclaimer: I only have ASP knowledge, I really don't know the best practices.

Answer 4

You can do it

course = trim (replace (request ("course"), "," & apos; "))