Wildcards in javascript with SQL

[Ray Allison]

Hi,

The objective is to have a form with text boxes for selection criteria
followed by an 'asp' displaying the query results. We would like to match
strings from the text boxes with table data beginning with the same strings.
Currently we are able to select exact matches but not like items (ie items
beginning with the same string).
Data from the form is passed to variables in the 'asp' as follows:
var varMake=Request.Form("txtMake");
var varModel=Request.Form("txtModel");

We then open a connection to an Access database and populate a recordset
with a list of stock using the following variables:
var adoConnection=Server.CreateObject("ADODB.Connection");
var adoRecordset;
var mySQL;

The value given to mySQL is:
var mySQL="SELECT * FROM tblStock WHERE Make = ' " + varMake + " ' AND
Model LIKE ' " + varModel + " ' ORDER BY PurchasePrice DESC";

This is then executed by:
adoRecordset=adoConnection.Execute(mySQL);

For the Make to be an exact match is fine. However, it would be disireable
for the Model to display like items. But using an asterix as a wildcard
does not work.
E.g. we can obtain exact model matches for models 'abc1', 'abc2' and 'abc3'
etc. but nothing is returned for 'abc* ' when we would expect all 'abcnn' to
be returned. Note though that the asterix earlier in the SQL, 'Select * ',
does work by selecting all fields.

Any help and suggestions would be greatly appreciated.

Thanks

Ray

 

[Lasse Reichstein Nielsen]

Any help and suggestions would be greatly appreciated.

My only suggestion is to ask in an SQL group. Your problem is in the SQL,
not the Javascript, so people in this group are not necessarily able to
help you (some might be, but you'll be more likely to find SQL experts
in another group).

I think Googleing after "sql LIKE wildcard" will give you suggestions
on how to solve the problem. I *think* you should use "%" instead of
"*" (from
<URL:http://epoch.cs.berkeley.edu:8000/sequoia/dba/montage/FAQ/SQL_howto.html>)

/L

 

[kaeli]

For the Make to be an exact match is fine. However, it would be disireable
for the Model to display like items. But using an asterix as a wildcard
does not work.

The wildcard for standard SQL is a percent sign.
I can't say Access uses standard SQL though. Give it a try.

select whatever from table where upper(whatever) like upper('%
somestring%')

would be case-insensitive as well for more matches.

-------------------------------------------------
~kaeli~
All I ask for is the chance to prove that money
cannot make me happy.
http://www.ipwebdesign.net/wildAtHeart
http://www.ipwebdesign.net/kaelisSpace
-------------------------------------------------

 

[Bryan Field-Elliot]

The value given to mySQL is:
var mySQL="SELECT * FROM tblStock WHERE Make = ' " + varMake + " ' AND
Model LIKE ' " + varModel + " ' ORDER BY PurchasePrice DESC";

This is then executed by:
adoRecordset=adoConnection.Execute(mySQL);

For the Make to be an exact match is fine. However, it would be disireable
for the Model to display like items. But using an asterix as a wildcard
does not work.
E.g. we can obtain exact model matches for models 'abc1', 'abc2' and 'abc3'
etc. but nothing is returned for 'abc* ' when we would expect all 'abcnn' to
be returned. Note though that the asterix earlier in the SQL, 'Select * ',
does work by selecting all fields.

I don't know what the wildcard character is for Microsoft SQL Server,
but on other databases, it's the LIKE keyword, with "%" as the wildcard,
example:

where Make like 'Ford%'

However there is a bigger problem with your script, you should never be
generating SQL statements with strings input directly from the client
(browser). This can lead to a security problem known as "SQL Injection".
Much better to use parameterized queries.

See here:

http://www.securiteam.com/securityreviews/5IP030K8AA.html

 

[Ray Allison]

Thanks, using percentage sign ' % ' instead of an asterix ' * ' has solved
the problem.

Again, Many thanks

Ray

 

[Ray Allison]

Thanks, using a percentage sign ' % ' instead of an asterix ' * ' has solved
the problem.

Again, Many thanks,

Ray

 

[Ray Allison]

Thanks, using the percentage sign ' % ' has solved the problem.

I hope the databases involved in our application are too insignificant for a
would be attacker. But thanks for the advice on 'SQL injection'.

Again, Many thanks,

Ray

 

[Steve van Dongen]

I hope the databases involved in our application are too insignificant for a
would be attacker. But thanks for the advice on 'SQL injection'.

You're assuming that the contents of the database is the goal. A SQL
injection attack may simply be the first step in breaking into your
system or network.

Regards,
Steve