SQL Injection Protection

[emozley]

Hi,

I have a table which has links at the top of each column to sort by. I am aware that this opens up the risk of SQL injection as someone can put whatever they want in the URL.

Am I correct in thinking this is a foolproof way of stopping it or are there still potential problems?

Thanks very much

Ed

<%

sort=Request.QueryString("sort")

Select Case sort
Case "cRouteName ASC"
sort = "cRouteName ASC"
Case "cRouteName DESC"
sort = "cRouteName DESC"
Case "dtDictEnd ASC"
sort = "dtDictEnd ASC"
Case "dtDictEnd DESC"
sort = "dtDictEnd DESC"
Case "nPriority ASC"
sort = "nPriority ASC"
Case "nPriority DESC"
sort = "nPriority DESC"
Case "cAuthorName ASC"
sort = "cAuthorName ASC"
Case "cAuthorName DESC"
sort = "cAuthorName DESC"
Case "cDeptName ASC"
sort = "cDeptName ASC"
Case "cDeptName DESC"
sort = "cDeptName DESC"
Case Else
sort = "cRouteName ASC"
End Select

'Execute the SQL. Select everything from jobs where the status is U and order by whatever column we have
'clicked on.
TBL.Open "SELECT * FROM Jobs WHERE cStatusChar='U' ORDER BY " & sort, DB

%>

 

[aspvbnetnerd]

If it is a Access database or MS SQL

TBL.Open "SELECT * FROM Jobs WHERE cStatusChar='U' ORDER BY " & Replace(sort, "'", "''"), DB

 

[Tarwn]

aspvbnetnerd: Right up until someone posts "x; DROP TABLE Jobs"

It's differant when your just protecting a string you plan on putting quotes around, when your just putting a number or field name straight into the query you need to do more than escape quotes, because there are no quotes around the entry to begin with.

emozley: I don't think you necessarally have to spell the whole thing out. One thing you might consider is getting rid of the ASC and DESC as part of the same field. Thatway you could havetwo fields, one the field name and one the direction.
At this point you could then make sure the direction is either ASC or DESC: If InStr("ASC,DESC",UCASE(Request.QueryString("dir"))) > 0
Then you could check the fieldname. Either check against a list of vald fieldname like above or do a regex check to find if there are any invalid characters in it.

The minus is that this method is probably less efficient than spelling it out in a SELECT statement. The plus is thatyou could easily re-use most or all of it for oter tables.

-T