Bad: Re: [Zope-dev] ZSQL using LIKE operator
Got it. Making the change now. Thanks for keeping an eye on this thread. What about the sqltest suggestion on posted on this thread? Or do sqltest and sqlvar handle DB calls in a similar fashion? Thanks -Original Message- From: Jon Franz [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 08, 2001 3:54 PM To: '[EMAIL PROTECTED]' Subject: Bad: Re: [Zope-dev] ZSQL using LIKE operator No, this is bad!! Do NOT do this - it will allow Bad characters in your SQL query that could allow mischievous people to tamper with your Db and possibly hack your box (depending upon what DB you are using, how it is configured, what user it runs as, etc) This is the whole reason the dtml-sqlvar tag exists - _Safe_ conversion to formats usable by your DB, including escaping of bad characters. instead, do This: SELECT * FROM table WHERE keywords LIKE dtml-sqlvar "'%' + my_var + '%'" type=string the expression inside the quotes will handle adding the %'s to the beginning and end of your string. Sorry about the correction, but this Can be a big security hazard... ~Jon Franz/'Coventry': http://www.zope.org/Members/Coventry Message: 9 Date: Thu, 08 Feb 2001 07:32:48 -0500 Subject: Re: [Zope-dev] ZSQL using LIKE operator From: Jens Vagelpohl [EMAIL PROTECTED] To: "Schmidt, Allen J." [EMAIL PROTECTED], [EMAIL PROTECTED] just write it out like: SELECT * FROM table WHERE keywords LIKE '%dtml-var name="my_var"%' jens ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope ) ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
RE: Re: [Zope-dev] ZSQL using LIKE operator
sqltest just creates the full string of the where clause segment for the test using the same kind of 'safe' sql-string logic as sqlvar: so you should be able to replace the value to test against with any valid python expression, such as the one below where the % operators are concatenated onto the variable holding the value you want to test against. :) As for the difference between dtml-sqlvar and dtml-var sqlquote (in case anyone is confused), an sqlvar tag requires a type value and will not only perform an sqlquote on the value being inserted into the statement, but will do any/all type conversion/stripping (letters from numeric values, etc) needed based upon the requested type. If anyone is concerned/puzzled by the security hazards I listed below, here is a URL describing problems associated with bad data used within queries and a mysql DB: http://www.mysql.com/doc/G/e/General_security.html See the bullet point beginning with 'Do not trust any data entered by your users.' Sorry if I seemed harsh in my original post, but security is my bread and butter, so I may tend to be Loud when I see something wrong... PS: In order to increase the safety of ZSQLMethods, maybe the basic dtml-var tag should be made illegal inside it? (forced usage of the safe form would break some existent code, possibly, but would avoid confusion such as this in general - and thus be safer) -Original Message- From: Schmidt, Allen J. [mailto:[EMAIL PROTECTED]] Sent: Friday, February 09, 2001 7:01 AM To: 'Jon Franz'; '[EMAIL PROTECTED]' Subject: Bad: Re: [Zope-dev] ZSQL using LIKE operator Got it. Making the change now. Thanks for keeping an eye on this thread. What about the sqltest suggestion on posted on this thread? Or do sqltest and sqlvar handle DB calls in a similar fashion? Thanks -Original Message- From: Jon Franz [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 08, 2001 3:54 PM To: '[EMAIL PROTECTED]' Subject: Bad: Re: [Zope-dev] ZSQL using LIKE operator No, this is bad!! Do NOT do this - it will allow Bad SNIP ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
RE: Re: [Zope-dev] ZSQL using LIKE operator
Not taken as being harsh from where I stand! The more we know...the better! Thanks for the guidelines and the URL. It has been passed on to the group that handles the MySQL on our server. Have not started using it for Production, but will be soon. From a recent post, I noticed that this topic might be better suited for the normal Zope list. Would this be accurate? Comments welcome on accepted topics. Thanks Allen -Original Message- From: Jon Franz [mailto:[EMAIL PROTECTED]] Sent: Friday, February 09, 2001 10:18 AM To: 'Schmidt, Allen J.' Cc: '[EMAIL PROTECTED]' Subject: RE: Re: [Zope-dev] ZSQL using LIKE operator sqltest just creates the full string of the where clause segment for the test using the same kind of 'safe' sql-string logic as sqlvar: so you should be able to replace the value to test against with any valid python expression, such as the one below where the % operators are concatenated onto the variable holding the value you want to test against. :) As for the difference between dtml-sqlvar and dtml-var sqlquote (in case anyone is confused), an sqlvar tag requires a type value and will not only perform an sqlquote on the value being inserted into the statement, but will do any/all type conversion/stripping (letters from numeric values, etc) needed based upon the requested type. If anyone is concerned/puzzled by the security hazards I listed below, here is a URL describing problems associated with bad data used within queries and a mysql DB: http://www.mysql.com/doc/G/e/General_security.html See the bullet point beginning with 'Do not trust any data entered by your users.' Sorry if I seemed harsh in my original post, but security is my bread and butter, so I may tend to be Loud when I see something wrong... PS: In order to increase the safety of ZSQLMethods, maybe the basic dtml-var tag should be made illegal inside it? (forced usage of the safe form would break some existent code, possibly, but would avoid confusion such as this in general - and thus be safer) -Original Message- From: Schmidt, Allen J. [mailto:[EMAIL PROTECTED]] Sent: Friday, February 09, 2001 7:01 AM To: 'Jon Franz'; '[EMAIL PROTECTED]' Subject: Bad: Re: [Zope-dev] ZSQL using LIKE operator Got it. Making the change now. Thanks for keeping an eye on this thread. What about the sqltest suggestion on posted on this thread? Or do sqltest and sqlvar handle DB calls in a similar fashion? Thanks -Original Message- From: Jon Franz [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 08, 2001 3:54 PM To: '[EMAIL PROTECTED]' Subject: Bad: Re: [Zope-dev] ZSQL using LIKE operator No, this is bad!! Do NOT do this - it will allow Bad SNIP ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
Re: [Zope-dev] ZSQL using LIKE operator
just write it out like: SELECT * FROM table WHERE keywords LIKE '%dtml-var name="my_var"%' jens on 2/8/01 7:17, Schmidt, Allen J. at [EMAIL PROTECTED] wrote: I have been through the docs, searched a variety of locations, and cannot find anything on how to resolve a query which I need to read: SELECT * FROM table WHERE keywords LIKE '%keywords_variable%' dtml-sqltest has 'op=like' and when set to 'type=string' produces the LIKE operation in the query, with single quotes, but I cannot get it to 'wrap' the keywords with the percent characters and THEN the single quotes to produce that which I have in the query above. Ideally what I need to produce would be the query to search through some text in several fields by the keyword_variable (IF any keywords are provided) AND/OR within a specific category of information. If I can get the syntax to solve the above situation I think I can get dtml-sqlgroup to sort out what information is provided and construct the query accordingly. Sorry for the length of this newbie question but I am stumped on this one. And, so ends my lurking status. Thanks! Allen ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope ) ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
RE: [Zope-dev] ZSQL using LIKE operator
Yup...You're right. I found an item that had a single quote in it. I tried it and the query blew up. then I added parts you suggest, and bingo...works fine. Thanks! To get around another problem I noticed... I have a keywords text field to search through everything in every category. On the same page, I have links which pass on the category number to the ZSQL method. The keywords FORM uses a hidden category field which I set to ="" -- worked fine. But if I used the URL to jump right to the category listings, there was no keyword to pass. I just set an empty property named 'keywords' and then if the URL search is used, keywords has a 'value'. I am sure there is a more elegant way to do this..but it works fine. Thanks again guys! Allen -Original Message- From: Casey Duncan [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 08, 2001 11:11 AM To: Jens Vagelpohl Cc: Schmidt, Allen J.; [EMAIL PROTECTED] Subject: Re: [Zope-dev] ZSQL using LIKE operator Jens Vagelpohl wrote: just write it out like: SELECT * FROM table WHERE keywords LIKE '%dtml-var name="my_var"%' jens on 2/8/01 7:17, Schmidt, Allen J. at [EMAIL PROTECTED] wrote: I have been through the docs, searched a variety of locations, and cannot find anything on how to resolve a query which I need to read: SELECT * FROM table WHERE keywords LIKE '%keywords_variable%' dtml-sqltest has 'op=like' and when set to 'type=string' produces the LIKE operation in the query, with single quotes, but I cannot get it to 'wrap' the keywords with the percent characters and THEN the single quotes to produce that which I have in the query above. Ideally what I need to produce would be the query to search through some text in several fields by the keyword_variable (IF any keywords are provided) AND/OR within a specific category of information. If I can get the syntax to solve the above situation I think I can get dtml-sqlgroup to sort out what information is provided and construct the query accordingly. Sorry for the length of this newbie question but I am stumped on this one. And, so ends my lurking status. Thanks! Allen SELECT * FROM table WHERE keywords LIKE '%dtml-var name="my_var" sql_quote%' would be a safer bet. Otherwise a value of my_var with a single quote in it wouldn't work. -- | Casey Duncan | Kaivo, Inc. | [EMAIL PROTECTED] `-- ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
Bad: Re: [Zope-dev] ZSQL using LIKE operator
No, this is bad!! Do NOT do this - it will allow Bad characters in your SQL query that could allow mischievous people to tamper with your Db and possibly hack your box (depending upon what DB you are using, how it is configured, what user it runs as, etc) This is the whole reason the dtml-sqlvar tag exists - _Safe_ conversion to formats usable by your DB, including escaping of bad characters. instead, do This: SELECT * FROM table WHERE keywords LIKE dtml-sqlvar "'%' + my_var + '%'" type=string the expression inside the quotes will handle adding the %'s to the beginning and end of your string. Sorry about the correction, but this Can be a big security hazard... ~Jon Franz/'Coventry': http://www.zope.org/Members/Coventry Message: 9 Date: Thu, 08 Feb 2001 07:32:48 -0500 Subject: Re: [Zope-dev] ZSQL using LIKE operator From: Jens Vagelpohl [EMAIL PROTECTED] To: "Schmidt, Allen J." [EMAIL PROTECTED], [EMAIL PROTECTED] just write it out like: SELECT * FROM table WHERE keywords LIKE '%dtml-var name="my_var"%' jens ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
Re: [Zope-dev] ZSQL using LIKE operator
Schmidt, Allen J. writes: how to resolve a query which I need to read: SELECT * FROM table WHERE keywords LIKE '%keywords_variable%' dtml-sqltest has 'op=like' and when set to 'type=string' produces the LIKE operation in the query, with single quotes, but I cannot get it to 'wrap' the keywords with the percent characters and THEN the single quotes to produce that which I have in the query above. dtml-sqltest expr="'%%%s%%' % keywords_variable" column=keywords_variable ... Dieter ___ Zope-Dev maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )
