Re: Parsing a boolean search string for SQL query

And just exactly how are you going to account for NOT? Remember, the syntax for NOT is something like:

SELECT * FROM movie WHERE title LIKE 'A%' AND title NOT LIKE 'Ab%'
[download]

And the fact that i chose to use LIKE instead of = says something as well ... your interface is going to get hairy with the approach you have chosen.

Instead of parsing some user-given string, you should restrict their interface to lists and check boxes/radio buttons. Then you can create your SQL statement with something like SQL::Abstract.

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

Comment on Re: Parsing a boolean search string for SQL query Download Code

Replies are listed 'Best First'.
Re: Re: Parsing a boolean search string for SQL query by tachyon (Chancellor) on Sep 01, 2003 at 17:24 UTC
I love people who put LIKE '$user_query%' into their SQL, especially when they don't restrict the query charset and/or result set size properly. It makes data mining really easy. You just query for % and generally get a database dump..... Not suggesting that jeffa would for a moment, just a relevant note. Here is one example where you can easily extract the database. Here is another where the lack of server side checking lets you ask for the entire database (with a correctly crafted POST) and get it. For example this snippet of HTML will dump the ENTIRE braintrack database into your browser (the thank you for attending our source code is kinda funny in context)..... `<FORM action="http://www.braintrack.com/search.htm" METHOD=POST> <INPUT TYPE="hidden" NAME="term1" value="%"> <INPUT TYPE="hidden" NAME="term2" value=""> <INPUT TYPE="hidden" NAME="term3" value=""> <INPUT TYPE="hidden" NAME= "join" value="and"> <INPUT TYPE="hidden" NAME="top" value="7000"> <INPUT TYPE=SUBMIT VALUE="Search"> </FORM>` [download] I doubt that this is what was intended when they put the DB online. For the record I emailed braintrack.com about this issue some months ago but they have not done anything about it (or actually had the courtesy to reply, for that matter). cheers tachyon s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print	[reply] [d/l]
3Re: Parsing a boolean search string for SQL query by jeffa (Bishop) on Sep 01, 2003 at 17:50 UTC
Nope. You got me: http://dvd.unlocalhost.com/?list_m=%25&go_movie=By+Movie That should only show movies that start with some string ... but i did not check for the fact that the user could provide their own GET query and specify only '%' ... of course, for this application ... it is OK, but that still does not excuse the potential security hole i dug. For the record, i am rewiting that interface with Template and Class::DBI. It's slow going, because i have tables in the 3rd Normal Form, and Class::DBI currently does not handle selections across tables as well as i would like. But as soon as i am finished, i will share it with the Monastery (i have not written a large tutorial in a while ...). UPDATE: (for anyone interested in such) My DVD CGI search form uses POST requests. This (in the past) meant that web bot scripters needed to look at the source and munge out all the parameters in order to re-create the proper form query. I used the Web Developer Plugin for the Firebird browser to literally convert POST requests to GET requests on a loaded HTML page. Then, when you submit, the form parameters are sent as a GET request, allowing you to cut-n-paste the URL such as i did above. Of course, this only works if the CGI script you are querying accepts both GET and POST requests. Not all do. (cough cough) jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply]
Re: 3Re: Parsing a boolean search string for SQL query by tachyon (Chancellor) on Sep 01, 2003 at 17:58 UTC
I guess it is easy to forget.... For many sites the database it their major asset. Unfortunately when people put databases online they forget just how potentially exposed they are. I put a short snippet in the original post that dumps a 7000 record DB into the browser (mainly because I had the courtesy to tell braintrack.com about the problem but they did not have the courtesy to reply - they also have not fixed the issue). As for GET/POST they offer no protection whatsoever. You don't even need LWP, you can just use HTML to do your bidding in many cases. cheers tachyon s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print	[reply]