3

I'm using SQL Server 2005 with classic ASP and on a form repost (I post back to the same page), I replace each text field as follows:

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

The problem with this is if I have to repost the form multiple times in case of validation errors, the tick marks I replace multiply.

Is there another way to safely vet the string fields without doing this kind of replace?

Caveatrob
  • 12,667
  • 32
  • 107
  • 187
  • There is a regex in [this answer](http://stackoverflow.com/questions/1609947/regex-for-replacing-a-single-quote-with-two-single-quotes/1609978#1609978) (to a different question) that claims to take care of the double-tick problem. – Robert Harvey Mar 04 '11 at 00:14

4 Answers4

5

you better use a parametrized 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 you are completely safe with sql injection

ulluoink
  • 2,775
  • 2
  • 17
  • 22
4

Only replace the ' for use in the sql string. (which you should better do with parameterized queries..)

Gabriele Petrioli
  • 191,379
  • 34
  • 261
  • 317
  • I do use parameterized queries. Does that mean I don't need to escape the SQL ticks? – Caveatrob Mar 04 '11 at 06:00
  • @Caveatrob, yep. Parameterized queries dont need escaping for the string value.. have a look at http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html – Gabriele Petrioli Mar 04 '11 at 09:31
3

The easiest way would be to only do the SQL escaping when you're actually inserting into the database:

course = trim(request("course"))

Make a SafeSQL function:

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

Then, when you're inserting:

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

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

thirtydot
  • 224,678
  • 48
  • 389
  • 349
  • The only issue with this is that if someone has an apostrophe in their name you end up with Marcus O''Donnel so its best to replace with a ASCII value for the grammar. E.g. &quote; or ' – Chris Dowdeswell Mar 04 '11 at 09:47
  • @Chris Dowdeswell: No, that's not how it works here. If you send `'Marcus O''Donnel'`, then `Marcus O'Donnel` is what actually gets inserted into the database. In other words it's escaping the quotes. – thirtydot Mar 04 '11 at 10:02
0

you can do this

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

Dee
  • 1,432
  • 1
  • 9
  • 8
  • Bad ideia... yes, it will prevent SQL injections, and it wont accumulate between reposts, but will mess the data in a very ugly way. "'" is only converted to ' when data is displayed in HTML body. It will be displayed as ' in text fields, the database, and pretty much everywhere. If such approach was taken, ` (backtick) or ' (acute accent) is much more desirable replacement. – MestreLion Mar 04 '11 at 16:20
  • My answer is a valid solution. It's up to the original poster to know how it will be implemented. If it is going to be a problem in a text rendering then he could do another replacement when the data is retrieved. backtick is another option too. – Dee Mar 04 '11 at 21:27