MSSQL LIKE and IN statements in ADO problem

[ram0812]

Helo guys,
I am trying to query the MSSQL DB using ADO.
I am not able to make the LIKE statement fetch the correct results.
Can anyone tell me what I need to do to get this working?
Below is the code snippet:

import win32com.client
const = win32com.client.constants

#conn = establish SQL connection
cmd = win32com.client.Dispatch("ADODB.Command")
cmd.ActiveConnection = conn

name = '@fname'
value = "'raj"
p=cmd.CreateParameter(name, const.adVarchar, Value=value)
cmd.Parameters.Append(p)
cmd.CommandText = \
"SELECT * FROM tb_name WHERE firstname LIKE @fname"
cmd.CommandType = const.adCmdText
(rs, dummy) = cmd.Execute()
while not rs.EOF:
print rs.Fields('firstname').Value
rs.MoveNext()
rs.Close()

I originally was using the '%?%' symbol but that did not work and now,
i changed it to @fname but this returns a traceback telling that I need
to declare @fname.

Also, I have another probelm with using the "IN" SQL statement.

I appreciate your help in advance,

Thank you,
Raja Raman

 

[gregarican]

Can't you get rid of the Create Parameter part and directly pass along
the value you are looking for? Something like...

name = 'raj'
cmd.CommandText= \
"SELECT * FROM tb_name WHERE firstname LIKE %%%s" % name

This way the value of the name variable gets passed along when the
CommandText method is invoked. BTW, this looks too painfully much like
Visual Basic than Python Just kidding (kind of)

 

[gregarican]

Sorry forgot to explain that with the string substitution stuff you can
escape the percent sign by doubling it up. In my example I wanted to
retain the leading percent sign before the value, in this case I wanted
LIKE %raj to appear. So I doubled it up. That's why there are three
percent signs in a row. The last one is the one associated with the
string substitution for the name variable. Make sense?

 

[Steve Holden]

Sorry forgot to explain that with the string substitution stuff you can
escape the percent sign by doubling it up. In my example I wanted to
retain the leading percent sign before the value, in this case I wanted
LIKE %raj to appear. So I doubled it up. That's why there are three
percent signs in a row. The last one is the one associated with the
string substitution for the name variable. Make sense?

Now Google for "sql injection vulnerability" and tell us why this is a
bad idea.

regards
Steve

 

[Raja Raman Sundararajan]

Yes, Steve you have a very good point.
Gregarcian, I am using the parameterized SQL to avoid such
vulunerability.
for example in your example use
name = "%'WAITFOR DELAY '00:00:03'--%"
and directly substitute it to the statement
"select * from table_name where name like '%s' " % (name)
The server will have to wait for three seconds and will return all the
rows in the table,
which is unwanted.
I am trying to use parameterized statements to avoid these sort of SQL
injection problems
and have not managed yet to fix the LIKE and IN statement problems.

/Raja Raman

 

[gregarican]

Now Google for "sql injection vulnerability" and tell us why this is a
bad idea.

The original poster didn't specify if they were writing
production-level code on in Internet-facing server so I didn't exactly
infer a context. You are correct in your statement. I was just pointing
out how substitutions operate if they were indeed an option.

 

[Raja Raman Sundararajan]

Hi Gregarican,
I am the original poster and yes this is a production code level
problem.
Do u have inputs for a solution?
/Raja Raman

 

[Roger Upole]

Helo guys,
I am trying to query the MSSQL DB using ADO.
I am not able to make the LIKE statement fetch the correct results.
Can anyone tell me what I need to do to get this working?
Below is the code snippet:

import win32com.client
const = win32com.client.constants

#conn = establish SQL connection
cmd = win32com.client.Dispatch("ADODB.Command")
cmd.ActiveConnection = conn

name = '@fname'
value = "'raj"
p=cmd.CreateParameter(name, const.adVarchar, Value=value)
cmd.Parameters.Append(p)
cmd.CommandText = \
"SELECT * FROM tb_name WHERE firstname LIKE @fname"
cmd.CommandType = const.adCmdText
(rs, dummy) = cmd.Execute()
while not rs.EOF:
print rs.Fields('firstname').Value
rs.MoveNext()
rs.Close()

I originally was using the '%?%' symbol but that did not work and now,
i changed it to @fname but this returns a traceback telling that I need
to declare @fname.

Also, I have another probelm with using the "IN" SQL statement.

I appreciate your help in advance,

Thank you,
Raja Raman

The wildcards are part of your input string, so you'd need
value = "%raj%" (looks like there was an extraneous single
quote in the original) and your sql would be
"SELECT * FROM tb_name WHERE firstname LIKE ?"
Named parameters are usually used for calling
stored procedures. As far as I know, you can't use them
with plain Sql.

hth
Roger

 

[Peter A. Schott]

Well, the raw TSQL would be:

select * from tb_name where firstname like '%raj%'

I think that would more translate to:

name = "raj"
cmd.CommandText = "SELECT * FROM tb_name WHERE firstname like '%%%s%%'" % name

Perhaps issuing a print statement of the CommandText would help for future runs
to determine if this is valid TSQL syntax?

-Pete

 

[Raja Raman Sundararajan]

Hello Steve, Roger and Pete,
Nice to read your reply. Well, I can do an assert check for
integers and then filter out hazardous SQL injection characters for
varchars and do a direct substitution of the filtered values with the
SQL statement.

But by using ADO, input strings can be treated as what they are
intended to be by adding values to the CreateParameter statement whe we
do an Execute.
This way I thought I need not care about what kind of input I get from
the client, and I will be able to use them blindly to form a dynamic
query.
Thus producing a more general solution for SQL injection prone areas.

Also, when it comes to filtering of hazardous characters, I think its
better to allow only those characters one needs than to filter out SQL
injection specific characters.
But the problem is we have all kinds of special characters stored in
our DB and filtering out characters will only result in wrong output.
So, I have to rule out this case.

With the DB API, yes I had a quick look at the code and the dynamic
formation of the SQL statement does not seem to have support for IN
statements.
I am not sure about the LIKE statement though. Maybe, they do support
it. So, I need to take a closer look at the adoapi.py file before
concluding.

To the concerned:works just fine, the result set contains all the first names that
contains 'raj'
How should we do this using createparameter in python?
There should be a wasy toa chieve this in ADO using python.
C# has a way to do this using ADO.Net.

Please keep providing inputs, in the mean time I will also do some
research on this problem and get back to you if I find a solution.

Thanks!
/Raja Raman

 

[Raja Raman Sundararajan]

This does not seem to work well Rogerresult is 0 where I expected 4
/Raja Raman

 

[gregarican]

Thanks. Please keep us posted. For some of my potentially exposed areas
I was just doing regex lookups against the input parameter to filter
out possible SQL injection keywords. Obviously not as elegant and
efficient as using ADO parameters to strictly define the data that
should be coming into the SQL statement. Playing around with the code
you provided yesterday I had problems using an ADO parameter as a
condition of the SQL LIKE statement. Not sure if that's an ADO
limitation, a Python ADO limitation, or my relative ignorance

 

[Raja Raman Sundararajan]

Ok guys! The problem seems to be much easier to be solved than first
thought. -->Shoot<--
Using the correct CreateParameter statement seems to do the trick.
For example creating the parameter as
cmd.CreateParameter(name,const.adVarChar, const.adParamInput, Size=16,
Value=value)
# Name, Type, Direction, Size, Value

works pretty good with the LIKE statement

For the IN statement I have not yet found a good way. As of now
in am looping through the values and creating various parameters
Snippet:
##
##Global initialization
##
typeMap= {
types.IntType: const.adInteger,
types.LongType: const.adBigInt,
}

query = "SELECT * FROM tb_name WHERE firstname IN %(in_params)"

##
##add parameters and construct the ? values for the in statements
##Note the code below is a pseudo type thing and can contain syntax
errors
##
in_parameters = [1,2,3,4,5]
n_index = 0
in_params = ''

for i in in_parameters:
in_params += '?,'
name = 'name_%s' % i
p=cmd.CreateParameter(name, typeMap[type(i)], const.adParamInput,
Size=16, Value=i) # Name, Type, Direction, Value
cmd.Parameters.Append(p)
query = query % {'in_params': in_params[:-1]}

Any inputs to improve the IN statement logic?
My dream is to use just one create parameter for the SQL list
so that the query looks like
query = "SELECT * FROM tb_name WHERE firstname IN ?"
Nice and easy...


Thanks in advance!
/Raja Raman

 

[gregarican]

The IN statement logic is a good mind exercise if there are multiple
parameters that needed to be brought in. Below is the code that fixed
the LIKE statement logic where you needed an ADO parameterized query
used. Apparently the percent signs don't have to be referenced anywhere
in the code, as my tests ran successfully without them:

-------------------------------------------------
import win32com.client
from adoconstants import *


conn = win32com.client.Dispatch(r'ADODB.Connection')
conn.ConnectionString = "Driver={SQL
Server};Server=(local);Database=myDB;Trusted_Connection=yes;"
conn.Open()
if conn.state == adStateOpen:
print "Connected to database..."
else:
print "Not connected!"
exit
cmd=win32com.client.Dispatch(r'ADODB.Command')
cmd.ActiveConnection=conn

name = '@fname'
value = 'raj'
param=cmd.CreateParameter(name, adVarChar, adParamInput, 200, value)
cmd.Parameters.Append(param)
cmd.CommandText = "SELECT first, last FROM myTable WHERE first like ?"
cmd.CommandType = adCmdText
(rs, dummy) = cmd.Execute()
rowCount = 0
while not rs.EOF:
print rs.Fields('first').Value, rs.Fields('last').Value
rowCount=rowCount+1
rs.MoveNext()
print "%s records returned." % rowCount
rs.Close()

 

[Raja Raman Sundararajan]

Hi Gregarican,
Thanks for sharing your code. One needs to add the % signs if one
wants to do wildcard searches using LIKE in the SQL server.
Do as Roger and Steve suggested '%raj%', now you can find the names
containing the word raj anywhere in the column.
just value = 'raj' is only going to fetch you fnames that == 'raj'
Originally my problem was using the LIKE statement itself. But I guess
you already know.


/Raja Raman

 

[Steve Holden]

Raja Raman Sundararajan wrote:
[...]

Any inputs to improve the IN statement logic?
My dream is to use just one create parameter for the SQL list
so that the query looks like
query = "SELECT * FROM tb_name WHERE firstname IN ?"
Nice and easy...

Some DBAPI modules will indeed allow you to use a list or set parameter
for this purpose, but not all.

regards
Steve

 

[gregarican]

Hi Gregarican,
Thanks for sharing your code. One needs to add the % signs if one
wants to do wildcard searches using LIKE in the SQL server.
Do as Roger and Steve suggested '%raj%', now you can find the names
containing the word raj anywhere in the column.
just value = 'raj' is only going to fetch you fnames that == 'raj'
Originally my problem was using the LIKE statement itself. But I guess
you already know.

Duhhh on my part. A little behind the curve as I'm recouping from adult
chicken pox. Coding from the sickbed isn't ideal I suppose