ASP variable SQL error

Question

I am trying to use an ASP variable to determine how the sql is ordered.

ASP SQL ERROR LINE:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Text Driver] Syntax error (missing operator) in query expression '& supplier_name &'.

/junk/airsearch/search.htm, line 106

Relevant line:

conDB = "SELECT * FROM mul.csv WHERE ucase(supplier_name) LIKE ucase('%"+src_supplier_name+"%') AND ucase(aircraft_type) LIKE ucase('%"+src_aircraft_type+"%') ORDER BY & src_order & "

ASP CODE:

<%
        Dim connectString, connect, conDB, sconDB, lDB, con, scon, lcon, src_ccn, src_state, src_order
        connectString = "Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=" & Server.MapPath("data")
              src_supplier_name = Request.QueryString("supplier_name")
              src_aircraft_type = Request.QueryString("aircraft_type")
              src_state = Request.QueryString("state")


            src_order = "supplier_name"


        set connect = Server.CreateObject("ADODB.connection")
        connect.open connectString

        if src_state = "" then
            conDB = "SELECT * FROM mul.csv WHERE ucase(supplier_name) LIKE ucase('%"+src_supplier_name+"%') AND ucase(aircraft_type) LIKE ucase('%"+src_aircraft_type+"%') "   
            lDB = "SELECT * FROM mul.csv WHERE ucase(supplier_name) LIKE ucase('%"+src_supplier_name+"%')" 
        elseif src_state = "any" then
            conDB = "SELECT * FROM mul.csv WHERE ucase(supplier_name) LIKE ucase('%"+src_supplier_name+"%') AND ucase(aircraft_type) LIKE ucase('%"+src_aircraft_type+"%') ORDER BY & src_order & "   
            lDB = "SELECT * FROM mul.csv WHERE ucase(supplier_name) LIKE ucase('%"+src_supplier_name+"%') AND ucase(aircraft_type) LIKE ucase('%"+src_aircraft_type+"%') ORDER BY & src_order & " 
        else 
            conDB = " SELECT * FROM mul.csv WHERE ucase(state) LIKE ucase('%"+src_state+"%') AND ucase(supplier_name) LIKE ucase('%"+src_supplier_name+"%') AND ucase(aircraft_type) LIKE ucase('%"+src_aircraft_type+"%')"
            lDB = " SELECT * FROM mul.csv WHERE ucase(state) LIKE ucase('%"+src_state+"%') AND ucase(supplier_name) LIKE ucase('%"+src_supplier_name+"%') AND ucase(aircraft_type) LIKE ucase('%"+src_aircraft_type+"%')"
        end if

        sconDB = "SELECT * FROM mul.csv"    

        set con = connect.execute(conDB)
        set scon = connect.execute(sconDB)
        set lcon = connect.execute(lDB)
%>

What you are doing with the src_state, src_supplier_name, and src_aircraft_type makes your code vulnerable to Sql Injection attacks. You're practically begging to get hacked. — Joel Coehoorn
– Joel Coehoorn, Commented Jan 31, 2013 at 4:22

cha · Accepted Answer · 2013-01-31 04:11:16Z

1

try to modify like this:

conDB = "SELECT * FROM mul.csv WHERE ucase(supplier_name) LIKE ucase('%" & src_supplier_name & "%') AND ucase(aircraft_type) LIKE ucase('%" & src_aircraft_type & "%') ORDER BY " & src_order

answered Jan 31, 2013 at 4:11

cha

10.4k1 gold badge20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sephiith Over a year ago

Isn't there suppose to be an ending " for the entire sql statement?

Joel Coehoorn Over a year ago

@sephiith Sql doesn't care about that. There is supposed to be a " ending in for string literals in your vb code. %') ORDER BY is a string literal. src_order is not.

user447356 Over a year ago

SQL Injection is worth mentioning in this context.

Collectives™ on Stack Overflow

ASP variable SQL error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related