2

I'm working with some legacy code and need help creating a sql parameter and associating it with a query string. 

val = request.querystring("number1")
sql = "select * from table where table.number = ' &val & "'"

How do i create a parameter to avoid sql injection?

runners3431
  • 1,425
  • 1
  • 13
  • 30
  • 1
    Possible duplicate of [How to use ASP variables in SQL statement](http://stackoverflow.com/questions/20659972/how-to-use-asp-variables-in-sql-statement) – user692942 Apr 19 '16 at 09:01
  • 1
    Another example - http://stackoverflow.com/a/22037613/692942 – user692942 Apr 19 '16 at 09:03

1 Answers1

3

A quick solution - if your querystring value is numeric - is to use Cint() - which changes the querystring into an integer - eg

val = cint(request.querystring("number1"))
sql = "select * from table where table.number = " & val

If someone tries a sql injection by using a non numeric querystring value it will throw a type mismatch error and the database query will not be executed.

If you want to use something more complex than an integer then you should look at parameterised queries - there are plenty of questions on SO which deal with this, eg this one

Parameterized query in Classic Asp

Community
  • 1
  • 1
John
  • 4,658
  • 2
  • 14
  • 23
  • will it matter what `Session.LCID` is used when using `cint`? For instance `Session.LCID = 4108` would produce a number like this `Server.URLEncode(1 000,00)`; whereas `Session.LCID = 2055` would output `Server.URLEncode(1’000.00)`. Related https://serverfault.com/a/853565/408254 – MeSo2 Jun 22 '23 at 01:13
  • @MeSo2 If you're using cint the input needs to be an integer with no commas, spaces, decimal points or anything else, just 1234567890, so in your example 1000. Anything else will throw a type mismatch error before the script attempts to execute a database query. Like I said, it's a quick hack, but if you want to use anything more complex that an integer in your querystring then you need to look at parameterised queries. – John Jun 22 '23 at 09:25
  • `cint` will convert 1000.6 to 1001. But put in other words, any number that gets sent to the URL and reused later need special handling before it can get used in a SQL string. – MeSo2 Jun 22 '23 at 14:10
  • @MeSo2 Exactly. In a lot of cases a querystring value will correspond to the primary key field in the database and it's a quick way of protecting against malicious code. If you have a website with URLs which contain ".asp?" it's almost guaranteed that it will be targeted by something like Asprox at some point. https://en.wikipedia.org/wiki/Asprox_botnet – John Jun 22 '23 at 17:48
  • Thank you on the heads-up on the Asprox botnet info. I had sites in php hacked --- it really is all about knowing how to best protect your site from invaders. I mostly still code in asp classic, I guess I like old stuff. – MeSo2 Jun 23 '23 at 01:14