Re: MSSQL LIKE and IN statements in ADO problem
Raja Raman wrote: > 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 :-) -- http://mail.python.org/mailman/listinfo/python-list
Re: MSSQL LIKE and IN statements in ADO problem
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 -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC www.holdenweb.com PyCon TX 2006 www.python.org/pycon/ -- http://mail.python.org/mailman/listinfo/python-list
Re: MSSQL LIKE and IN statements in ADO problem
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: MSSQL LIKE and IN statements in ADO problem
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() -- http://mail.python.org/mailman/listinfo/python-list
Re: MSSQL LIKE and IN statements in ADO problem
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 :-) -- http://mail.python.org/mailman/listinfo/python-list
Re: MSSQL LIKE and IN statements in ADO problem
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[i]) # 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: MSSQL LIKE and IN statements in ADO problem
This does not seem to work well Roger >>> value = '%raj%' >>> cmd.CommandText = "select * from table_name where firstname LIKE ?" result is 0 where I expected 4 /Raja Raman -- http://mail.python.org/mailman/listinfo/python-list
Re: MSSQL LIKE and IN statements in ADO problem
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: >>> value = '%raj%' >>> "select * from table_name where firstname LIKE '%s' " % value 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: MSSQL LIKE and IN statements in ADO problem
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 "gregarican" <[EMAIL PROTECTED]> wrote: > 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) -- http://mail.python.org/mailman/listinfo/python-list
Re: MSSQL LIKE and IN statements in ADO problem
<[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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 == Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News== http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups ---= East/West-Coast Server Farms - Total Privacy via Encryption =--- -- http://mail.python.org/mailman/listinfo/python-list
Re: MSSQL LIKE and IN statements in ADO problem
Raja Raman Sundararajan wrote: > Yes, the statement you tried is a valid statement > also > name = "%'WAITFOR DELAY '00:00:03'--%" "SELECT * FROM tb_name WHERE firstname LIKE '%s'" % name > > is also valid. > My question is how to use the LIKE statements using ADO.in python > :-| > Raja: I don't use ADO at all any more, but I suspect that the answer is to take the search parameter provided by the user, add a "%" at the beginning and the end, and then use the resulting value as a parameter to the query. I will shortly have to solve the same sort of problem, but in my case using the DBAPI. Let me know if this works. The problem, of course, is that a query parametere can only replace a SQL token, not a part of one. regards Steve -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC www.holdenweb.com PyCon TX 2006 www.python.org/pycon/ -- http://mail.python.org/mailman/listinfo/python-list
Re: MSSQL LIKE and IN statements in ADO problem
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: MSSQL LIKE and IN statements in ADO problem
Yes, the statement you tried is a valid statement also >>> name = "%'WAITFOR DELAY '00:00:03'--%" >>> "SELECT * FROM tb_name WHERE firstname LIKE '%s'" % name is also valid. My question is how to use the LIKE statements using ADO.in python :-| -- http://mail.python.org/mailman/listinfo/python-list
Re: MSSQL LIKE and IN statements in ADO problem
Steve Holden wrote: > 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. -- http://mail.python.org/mailman/listinfo/python-list
RE: MSSQL LIKE and IN statements in ADO problem
huh? This seems to work just fine for me... >>> name = '%raj%' >>> test = "SELECT * FROM tb_name WHERE firstname LIKE '%s'" % name >>> print test SELECT * FROM tb_name WHERE firstname LIKE '%raj%' -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] n.org]On Behalf Of gregarican Sent: Wednesday, January 18, 2006 11:34 AM To: python-list@python.org Subject: Re: MSSQL LIKE and IN statements in ADO problem 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) -- http://mail.python.org/mailman/listinfo/python-list The information contained in this message and any attachment may be proprietary, confidential, and privileged or subject to the work product doctrine and thus protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify me immediately by replying to this message and deleting it and all copies and backups thereof. Thank you. -- http://mail.python.org/mailman/listinfo/python-list
Re: MSSQL LIKE and IN statements in ADO problem
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: MSSQL LIKE and IN statements in ADO problem
gregarican wrote: > 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 -- Steve Holden +44 150 684 7255 +1 800 494 3119 Holden Web LLC www.holdenweb.com PyCon TX 2006 www.python.org/pycon/ -- http://mail.python.org/mailman/listinfo/python-list
Re: MSSQL LIKE and IN statements in ADO problem
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? -- http://mail.python.org/mailman/listinfo/python-list
Re: MSSQL LIKE and IN statements in ADO problem
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) -- http://mail.python.org/mailman/listinfo/python-list