So far I'm enjoying zope. I've rewritten several sites using it, and think it will make my work more efficient. One of my older sites stores material in database. I'm probably going to put everything in zope eventually, but for now I just want to improve the search on a (small MS Access) database. So I now use zope to communicate with the database, and have created a form to search it, though at the moment my form is limited to exact matches--e.g., if you search on "Environmental Ethics" you'll get a result, but not if you search "Ethics." In sql you can use LIKE %name% to let people search more flexibly. So how do I pass the %name% from the form to the method? Do I make the change to the form, or to the zsql method? if the form has a field like: <input type="text" name="CourseName" size=20> and the zsql method is: SELECT * FROM Courses WHERE (CourseName LIKE <dtml-sqlvar CourseName type=string> OR SemesterTaught LIKE <dtml-sqlvar SemesterTaught type=string> OR CourseType LIKE <dtml-sqlvar CourseType type=string> ) btw, the site is: http://appliedphilosophy.mtsu.edu/syllabusproject/index.html thanks, Robert
On Tue, 12 Feb 2002, Robert Hood, Ph.D. wrote:
In sql you can use LIKE %name% to let people search more flexibly.
So how do I pass the %name% from the form to the method? Do I make the change to the form, or to the zsql method?
You might find my How-To "Combining ZSQL Methods With Python Scripts" helpful. It's at http://www.zope.org/Members/TWilson/ZSQL_and_Python_Scripts I think you'll find that a little Python Script is the way to go when you want to start splitting the form input and searching for matches. -Tim -- Tim Wilson | Visit Sibley online: | Check out: Henry Sibley HS | http://www.isd197.org | http://www.zope.com W. St. Paul, MN | | http://slashdot.org wilson@visi.com | <dtml-var pithy_quote> | http://linux.com
[Tim Wilson]
On Tue, 12 Feb 2002, Robert Hood, Ph.D. wrote:
In sql you can use LIKE %name% to let people search more flexibly.
So how do I pass the %name% from the form to the method? Do I make the change to the form, or to the zsql method?
You might find my How-To "Combining ZSQL Methods With Python Scripts" helpful. It's at http://www.zope.org/Members/TWilson/ZSQL_and_Python_Scripts
I think you'll find that a little Python Script is the way to go when you want to start splitting the form input and searching for matches.
You don't need to go to a python script. Instead, include a input element in your form to hold the search phrase. Say you name that input field "searchphrase". Then in your zsql method, you write something like select * from table where name like '%&dtml-searchphrase;%' Instead of the entity syntax, you could substitute <dtml-var "REQUEST.searchphrase">, or just <dtml-var searchphrase> Just make sure to add searchphrase to the list of parameters for the zsql method. Finally, if the searchphrase value might have quotes in it, make sure to use the sql quoting option (check the docs for the exact syntax). Cheers, Tom P
Thanks for the help! Tom P wrote:
You don't need to go to a python script. Instead, include a input element in your form to hold the search phrase. Say you name that input field "searchphrase". Then in your zsql method, you write something like select * from table where name like '%&dtml-searchphrase;%'
YES--all I had to do was make some minor changes to my zsql and it now works, and thankfully, no scripting, like so: SELECT * FROM Courses WHERE (CourseName LIKE '%&dtml-CourseName;%' OR SemesterTaught LIKE '%&dtml-SemesterTaught;%' OR CourseType = <dtml-sqlvar CourseType type=string>); Robert
On Tue, Feb 12, 2002 at 11:26:54AM -0600, Robert Hood, Ph.D. wrote:
Thanks for the help!
Tom P wrote:
You don't need to go to a python script. Instead, include a input element in your form to hold the search phrase. Say you name that input field "searchphrase". Then in your zsql method, you write something like select * from table where name like '%&dtml-searchphrase;%'
YES--all I had to do was make some minor changes to my zsql and it now works, and thankfully, no scripting, like so:
SELECT * FROM Courses WHERE (CourseName LIKE '%&dtml-CourseName;%' OR SemesterTaught LIKE '%&dtml-SemesterTaught;%' OR CourseType = <dtml-sqlvar CourseType type=string>);
An aside to newbies: Make sure that you do some validation here. You need to be sure that CourseName and SemesterTaught do not contain an unquoted ' and possibly a sql-delimiter (usually ;). It might be a very good idea to simply strip all single quotes from these fields before you call you zsql method. Suppose you had a course Charlotte Bronte's Role in the Development of the Gender Awareness, and your user entered Charlotte Bronte's Role For CourseName. The ZSQL method will expand to SELECT * FROM Courses WHERE (CourseName LIKE 'Charlotte Bronte's Role' OR ... This will lead to a failure (ungrammatical SQL). On the other hand, if you have someone with a grudge, who enters Charlotte Bronte'); delete from Course; delete from course where CourseName='' well, you either better have a backup, or you have a nice little denial of service attack. Recap: If you cannot use a dtml-sqlvar form, you are responsible for making sure that sqlquoting is done (check sql_quote of dtml-var in appendix A in the Zope Book), or that input strings with single quotes in them never reach your zsql method. Always validate such input carefully. Jim Penny
Robert
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Thomas B. Passin writes:
.... select * from table where name like '%&dtml-searchphrase;%' Please do not forget "sql_quote" inside SQL strings. Otherwise, some malicious user may play havoc with your database (by closing the string, adding a very bad SQL command in which the string is reopened).
... Finally, if the searchphrase value might have quotes in it, make sure to use the sql quoting option (check the docs for the exact syntax). Do it, even when you think, there should be no quotes...
Dieter
[Dieter Maurer]> Thomas B. Passin writes:
.... select * from table where name like '%&dtml-searchphrase;%' Please do not forget "sql_quote" inside SQL strings. Otherwise, some malicious user may play havoc with your database (by closing the string, adding a very bad SQL command in which the string is reopened).
... Finally, if the searchphrase value might have quotes in it, make sure to use the sql quoting option (check the docs for the exact syntax). Do it, even when you think, there should be no quotes...
Amen... Tom P
participants (5)
-
Dieter Maurer -
Jim Penny -
Robert Hood, Ph.D. -
Thomas B. Passin -
Tim Wilson