By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,504 Members | 1,190 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,504 IT Pros & Developers. It's quick & easy.

Wildcards in javascript with SQL

P: n/a
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.Connectio n");
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


Jul 20 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
"Ray Allison" <ra*********@virgin.net> writes:
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
--
Lasse Reichstein Nielsen - lr*@hotpop.com
Art D'HTML: <URL:http://www.infimum.dk/HTML/randomArtSplit.html>
'Faith without judgement merely degrades the spirit divine.'
Jul 20 '05 #2

P: n/a
In article <Ur********************@newsfep2-win.server.ntli.net>,
ra*********@virgin.net enlightened us with...

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
-------------------------------------------------
Jul 20 '05 #3

P: n/a
Ray Allison wrote:

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/securityre...IP030K8AA.html


--

Bryan Field-Elliot
http://netmeme.org

Jul 20 '05 #4

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

Again, Many thanks

Ray

"Lasse Reichstein Nielsen" <lr*@hotpop.com> wrote in message
news:r8**********@hotpop.com...
"Ray Allison" <ra*********@virgin.net> writes:
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/se.../SQL_howto.htm
l>)
/L
--
Lasse Reichstein Nielsen - lr*@hotpop.com
Art D'HTML: <URL:http://www.infimum.dk/HTML/randomArtSplit.html>
'Faith without judgement merely degrades the spirit divine.'

Jul 20 '05 #5

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

Again, Many thanks,

Ray

"kaeli" <in********************@NOSPAMatt.net> wrote in message
news:MP************************@nntp.lucent.com...
In article <Ur********************@newsfep2-win.server.ntli.net>,
ra*********@virgin.net enlightened us with...

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
-------------------------------------------------

Jul 20 '05 #6

P: n/a
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

"Bryan Field-Elliot" <br***@netmeme.org> wrote in message
news:OeAgb.516450$cF.185962@rwcrnsc53...
Ray Allison wrote:

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/securityre...IP030K8AA.html


--

Bryan Field-Elliot
http://netmeme.org

Jul 20 '05 #7

P: n/a
On Wed, 8 Oct 2003 09:47:31 +0100, "Ray Allison"
<ra*********@virgin.net> wrote:
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/securityre...IP030K8AA.html


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
Jul 20 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.