ASP Classic SQL Query with two parameters

Question

This is SQL Query in ASP Classic:

mQry = "SELECT DISTINCT name FROM best WHERE invoice_num = "  & request.querystring("invoice_num") & " AND name LIKE '%" & request.querystring("org_name") & "%'"

I am not sure if this is correct or what. Based on this query, I need to display or output "name" based on the two input parameters which is the invoice_num and the org_name. I always got this error message:

Microsoft OLE DB Provider for Oracle error '80040e07' ORA-01722: invalid number.

What would be the right ASP Classic SQL query syntax for this..?

Before executing the SQL statement: `Response.Write(mQry)`. Post the resultant text back here. — gvee, Aug 29 '13 at 07:55
read about SQL injection and redesign your query and the way you work with SQL Statements in your application — ulluoink, Aug 29 '13 at 07:56
Maybe your invoice_num value is being treated as a string. Try cint(request.querystring("invoice_num")) - I've always found cint() very useful for getting rid of type mismatch errors. — John, Aug 29 '13 at 13:52
@John of course it is treated as a string because it is string concetenated. — ulluoink, Aug 30 '13 at 05:16
As suggested if you use Response.Write to show the SQL query being generated it will help people help you. Add the line Response.Write(mQry) : Response.End and report back with the query that is shown. — johna, Aug 31 '13 at 08:23

score 0 · Answer 1 · answered Sep 01 '13 at 07:10

Most likely you have self-referencing form (i.e. targeting the same page) and you don't check if the form was submitted before executing that code.

This code will perform such a check, plus have better protection against SQL Injection attacks:

Dim invoiceNumber
invoiceNumber = Request.QueryString("invoice_num")
If Len(invoiceNumber)>0 And IsNumeric(invoiceNumber) Then
    invoiceNumber = CLng(invoiceNumber)
    If invoiceNumber>0 Then
        mQry = "SELECT DISTINCT name FROM best WHERE invoice_num = "  & invoiceNumber & " AND name LIKE '%" & Replace(Request.Querystring("org_name"), "'", "''") & "%'"
        '...rest of the code here...
    End If
End If

score 0 · Answer 2 · answered Sep 01 '13 at 08:10

Request.QueryString("myval") reads a URL parameter called "myval".

So if you had a page called mypage.asp then you could specify parameters to it in the URL like this:

mypage.asp?myval=test1234

So with the above URL, if you call Request.QueryString("myval") is will return "test1234".

When you run your ASP page, are you sure you are specifying URL parameters for the two parameters in your query? It sounds like they are blank. This is ok for a string, but will fail for a numerical value.

Invoice_num and org_name should both be specified in the URL.

ASP Classic SQL Query with two parameters

2 Answers2

Linked