ASP Classic SQL Query Error Message - Invalid number

Question

I got a query using SQL inside ASP Classic, and the syntax that I got is shown below:

    mQry = "SELECT name FROM finals WHERE invoice_num = " &request.querystring("num") & " AND name LIKE '&" & request.querystring("nam") & "%'"
    response.write("Operating Unit")
    for each x in TestRally.fields
    response.write(": ")
    response.write(x.value)  '-- got error in this area...
    response.write("<br>")
    next
    response.write("<br>")
    TestRally.MoveNext

Based on the above mentioned syntax, what would be the best possible remedy for this..???

The error message I got so far is this:

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

Print out `mQry`. In my experience, looking at the query string after variable substitutions makes the fix obvious about 95% of the time. — Gordon Linoff, Aug 30 '13 at 01:09
I would think `&request.querystring("num")` is not a number. — FrankPl, Aug 30 '13 at 01:10
Or maybe my SQL query is wrong... so that I got that error message — Rally Cautiverio, Aug 30 '13 at 01:11
What DBMS are you using? MS Access? Microsoft SQL Server? MySQL? — Dai, Aug 30 '13 at 01:14
How are you using your 12c database? Are you using OLE-DB, ODBC (with `ADODB`) or something provided by Oracle? — Dai, Aug 30 '13 at 01:17
Set TestRally = CreateObject("Adodb.recordset") mQry="SELECT DISTINCT name FROM best WHERE invoice_num =" & request.querystring("num") & " AND name LIKE '%" & request.querystring("nam") & "%' " Set TestRally = oraConn.Execute(mQry) response.write("Operating Unit") for each x in TestRally.fields response.write(": ") response.write(x.value) '-- got error in this area..??? response.write("
") next Response.Write("
") TestRally.MoveNext TestRally.close oraConn.close Response.Write("
") — Rally Cautiverio, Aug 30 '13 at 01:21
possible duplicate of [ASP Classic SQL Query with two parameters](http://stackoverflow.com/questions/18505155/asp-classic-sql-query-with-two-parameters) — Shadow The Vaccinated Wizard, Sep 01 '13 at 07:20
Is this related to your [other question](http://stackoverflow.com/a/18490131/25163)? — AnonJr, Sep 03 '13 at 18:27

score 2 · Answer 1 · edited May 23 '17 at 12:21

2

Your code is wide-open to SQL injection attacks and that is partly the cause of your problem: you're not verifying (let alone sanitizing) your input that you use to formulate queries.

See this QA before continuing: What is SQL injection?

We can't tell you how to properly solve it without knowing what DBMS you're using, but a quick workaround is this:

Dim invoiceNum
invoiceNum = CInt( Request.QueryString("num") )
Dim name
name = Request.QueryString("nam")
name = Replace( name, "'", "''" )

Dim sql
sql = "SELECT name FROM finals WHERE invoice_num = " & invoiceNum & " AND name LIKE '&" & name & "%'"

edited May 23 '17 at 12:21

Community

1
1

answered Aug 30 '13 at 01:16

Dai

110,988
21
188
277

I am currently using Oracle 12c for the DBMS – Rally Cautiverio Aug 30 '13 at 01:18
@RallyCautiverio how is it not functioning? – Dai Aug 30 '13 at 01:29

score 0 · Answer 2 · answered Aug 30 '13 at 02:26

invoiceNum = CInt( Request.QueryString("num") )

is probably causing the overflow.

The invoice number you are passing is too big for an integer. Save the invoice number as string.

Using Dai's code, try this:

Dim invoiceNum
invoiceNum = Request.QueryString("num")
invoiceNum = Replace( invoiceNum, "'", "''")

Dim name
name = Request.QueryString("nam")
name = Replace( name, "'", "''" )

sql = "EXEC sp_executesql N'SELECT name FROM finals WHERE invoice_num = @invoice_num AND name LIKE ''&'' + @name + ''%'''"
sql = sql & ",N'@invoice_num varchar(20),@name varchar(255)',@invoice_num = '" & invoiceNum & "',@name = '" & name & "'"

You advice a wrong way to build a query concatenating parameter values to the command string instead of using parameters. Doing that is wrong and puts the security of your database in risk. — Yván Ecarri, Oct 13 '13 at 08:52

score 0 · Answer 3 · answered Sep 02 '13 at 03:32

Enable sql tracing or better 10046 tracing before executing the above statement and you will see which value is passed in for the invoice_num. This will create a trace file that details all the statements and how they are executed.

In addition post how the table has been defined.

score 0 · Answer 4 · answered Sep 16 '13 at 02:57

"SELECT name FROM finals WHERE invoice_num='" & request.querystring("num") & "' AND name = '" & request.querystring("nam") & "'"

This is the right SQL syntax for the query inside the ASP Classic instead of the above-mentioned SQL query that I have in my question.

ASP Classic SQL Query Error Message - Invalid number

4 Answers4