RE: Re: [Zope-dev] ZSQL using LIKE operator
Usually tag-syntax/usage is discussed more on the basic Zope list, but to be honest, I've always found the distinctions between the two blurred: people who use Zope sometimes post on dev, people who develop products and/or patches sometimes post on Zope-general, and people are developing _On_ Zope seem to choose one via the close-my-eyes-and- point technique. Also, occasionally a random monkey will Spam one or both lists, resulting not only in complaints and searches for a collar/muzzle for the monkey, but the hiding of all bananas in site. What is worse, though, is when a monkey brings up a licensing issue/argument/troll on either list - this usually results in the sad shaking of heads by all other monkeys viewing the dispute for the umpteenth time, and a lot of worthless Spam filling the list as monkeys who should be doing something useful (coding, bug-finding, and grooming their friends for fleas) waste effort by arguing the same old arguments first heard in 3E10 BC when Grog couldn't decide whether to share Fire with Bant and the rest of the tribe, keep it to himself quietly, or patent it and one-click shopping. (He decided inventing the patent office would take too long...) Anyway, enough silly humor - I don't think your posts are off-topic, especially since the issues raised do end up revealing a deficiency (in my opinion!) in ZSQLMethods - the legality of unsafe tags. > -Original Message- > From: Schmidt, Allen J. [mailto:[EMAIL PROTECTED]] > Sent: Friday, February 09, 2001 10:33 AM > To: 'Jon Franz' > Cc: '[EMAIL PROTECTED]' > Subject: 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 > < and tags >> ___ 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 and (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 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 < ___ 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 and (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 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 < ___ 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
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 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 '%%' > >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: [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%' > > 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. 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 )
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 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 '%%' > >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
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 '%%' > > 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%' > > > > 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 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 '%%' 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 )
Re: [Zope-dev] ZSQL using LIKE operator
Jens Vagelpohl wrote: > > just write it out like: > > SELECT * FROM table WHERE keywords LIKE '%%' > > 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%' > > > > 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 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 '%%' 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 )
Re: [Zope-dev] ZSQL using LIKE operator
just write it out like: SELECT * FROM table WHERE keywords LIKE '%%' 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%' > > 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 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 )
[Zope-dev] ZSQL using LIKE operator
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%' 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 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 )
