GET and POST dropping wildcard characters in DB query

[george.lengel]

Hello experts,

I have been struggling for days to solve this problem and every
suggestion I find via Google does not work for me. There is probably a
solution out there that will do what I want, but I probably have not
properly implemented the solutions I find.

I am trying to make a page to allow personnel the ability to search our
backend DB (which is Ingres 2.0) through a web interface. Ingres has a
cgi program called ICE that can receive SQL queries and return data to
format. I have lots of pages setup that query correctly and work really
well. The one I am struggling with is searching our parts database
because I want to allow wildcard searches. The Ingres wildcard is a %
sign so I can input 011% and it will find any item number that starts
with 011. I can use %011% and it will find any part with 011 in the
string. I only care about three fields item_no, description_1, and
description_2 for searching.

I have set up a simple form that queries properly using only hidden
variables and GET and no JS, but the wildcards have to be explicitly
entered in the form. I want use JS to validate the form and add the
wildcards for the user.

So, here is the incredibly ugly code I hacked together that almost
achieves what I want. The problem is using either GET or POST for the
action, the query the DB receives does not have "item_no like '011%' "
instead it is "item_no like '011' " so it returns ONLY the item with
item_no EXACTLY 011. Not helpful. If I use an alert to display the
send_string before the submit, it has wildcards in the string as I
expect but for some reason when I look in the DB logs and the apache
logs the query has no % and the GET statement passed to apache has no %
signs. What can I do to validate the form and get the strings passed to
the query with the % wildcards intact?

I want the user to only have to fill in what he cares about and then I
can validate and correctly compose the SQL query from there. The most
common example is to fill in the item_no with a string. I then make the
two description fields a single % so the query returns any items that
match the item_no without caring about the description fields. That is
why the code checks if all fields in one group are empty, it makes the
contains parameter for this field a single %.

Anyone who can steer me the correct way will be much appreciated.


<SCRIPT LANGUAGE="Javascript">
<!--
function checkme(parts) {
var start_string=document.forms[0].start_item_no.value;
var length_start_string=start_string.length;
var contains_string=document.forms[0].contains_item_no.value;
var length_contains_string=contains_string.length;
var end_string=document.forms[0].end_item_no.value;
var length_end_string=end_string.length;
if (length_start_string != 0) {start_string=start_string+"%"};
if (length_contains_string != 0)
{contains_string="%"+contains_string+"%"};
if (length_end_string != 0) {end_string="%"+end_string};
if (length_start_string== 0 && length_contains_string==0 &&
length_end_string==0) {contains_string="%"};
var
send_string="start_item_no="+start_string+"&contains_item_no="+contains_string+"&end_item_no="+end_string;
var start_desc1=document.forms[0].start_description_1.value;
var length_start_desc1=start_desc1.length;
var contains_desc1=document.forms[0].contains_description_1.value;
var length_contains_desc1=contains_desc1.length;
var end_desc1=document.forms[0].end_description_1.value;
var length_end_desc1=end_desc1.length;
if (length_start_desc1 != 0) {start_desc1=start_desc1+"%"};
if (length_contains_desc1 != 0)
{contains_desc1="%"+contains_desc1+"%"};
if (length_end_desc1 != 0) {end_desc1="%"+end_desc1};
if (length_start_desc1==0 && length_contains_desc1==0 &&
length_end_desc1==0) {contains_desc1="%"};
send_string=send_string+"&start_description_1="+start_desc1+"&contains_description_1="+contains_desc1+"&end_description_1="+end_desc1;
var start_desc2=document.forms[0].start_description_2.value;
var length_start_desc2=start_desc2.length;
var contains_desc2=document.forms[0].contains_description_2.value;
var length_contains_desc2=contains_desc2.length;
var end_desc2=document.forms[0].end_description_2.value;
var length_end_desc2=end_desc2.length;
if (length_start_desc2 != 0) {start_desc2=start_desc2+"%"};
if (length_contains_desc2 != 0)
{contains_desc2="%"+contains_desc2+"%"};
if (length_end_desc2 != 0) {end_desc2="%"+end_desc2};
if (length_start_desc2==0 && length_contains_desc2==0 &&
length_end_desc2==0) {contains_desc2="%"};
send_string=send_string+"&start_description_2="+start_desc2+"&contains_description_2="+contains_desc2+"&end_description_2="+end_desc2;
document.parts.action="http://192.168.254.238/cgi-bin/ice/macro_partsearch.html?"send_string;
document.parts.submit();
} //submit function
-->
</SCRIPT>
<FORM NAME="parts" METHOD="GET" onSubmit="javascript:checkme(this);">


<P>
Enter a string that the item number begins with
<INPUT TYPE=text NAME="start_item_no" VALUE=""><br>
Enter a string that the item number contains
<INPUT TYPE=text NAME="contains_item_no" VALUE=""><br>
Enter a string that the item number ends with
<INPUT TYPE=text NAME="end_item_no" VALUE="">
<br><p>
Enter a string that description 1 begins with
<INPUT TYPE=text NAME="start_description_1" VALUE=""><br>
Enter a string that description 1 contains
<INPUT TYPE=text NAME="contains_description_1" VALUE=""><br>
Enter a string that description 1 ends with
<INPUT TYPE=text NAME="end_description_1" VALUE="">
<br><p>
Enter a string that description 2 begins with
<INPUT TYPE=text NAME="start_description_2" VALUE=""><br>
Enter a string that description 2 contains
<INPUT TYPE=text NAME="contains_description_2" VALUE=""><br>
Enter a string that description 2 ends with
<INPUT TYPE=text NAME="end_description_2" VALUE="">
<br><p>
<INPUT TYPE="submit" VALUE="Find">

<CENTER>
</CENTER>
</FORM>

 

[Norman L. DeForest]

Hello experts,

I have been struggling for days to solve this problem and every
suggestion I find via Google does not work for me. There is probably a
solution out there that will do what I want, but I probably have not
properly implemented the solutions I find.

I am trying to make a page to allow personnel the ability to search our
backend DB (which is Ingres 2.0) through a web interface. Ingres has a
cgi program called ICE that can receive SQL queries and return data to
format. I have lots of pages setup that query correctly and work really
well. The one I am struggling with is searching our parts database
because I want to allow wildcard searches. The Ingres wildcard is a %
sign so I can input 011% and it will find any item number that starts
with 011. I can use %011% and it will find any part with 011 in the
string. I only care about three fields item_no, description_1, and
description_2 for searching.

I have set up a simple form that queries properly using only hidden
variables and GET and no JS, but the wildcards have to be explicitly
entered in the form. I want use JS to validate the form and add the
wildcards for the user.

[snip]

Before sending the query, any literal '%' characters in the URL must be
escaped as '%25'. See RFC 2396, Sections 2.4 to 2.4.3:
http://www.faqs.org/rfcs/rfc2396.html
ftp://ftp.isi.edu/in-notes/rfc2396.txt

 

[george.lengel]

If you mean instead of having the JS append % on the end of the search
variable I need the script to append %25 on the end, I had already
tried that and it still fails to get the data. In the case of using
GET the %25 is not present in the apache log so it looks like the URL
is still malformed without the %. When using POST, the %25 is there in
the URL but % does not show up in the SQL query log.

 

[VK]

document.parts.action="http://...?"send_string

Try:
document.parts.action="http://...?"+escape(send_string);