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=Reques t.Form("txtMode l");

We then open a connection to an Access database and populate a recordset
with a list of stock using the following variables:
var adoConnection=S erver.CreateObj ect("ADODB.Conn ection");
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=ad oConnection.Exe cute(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]

Re: Wildcards in javascript with SQL

"Ray Allison" <ray.allison@vi rgin.net> writes:
[color=blue]
> Any help and suggestions would be greatly appreciated.[/color]

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.berkel ey.edu:8000/sequoia/dba/montage/FAQ/SQL_howto.html> )

/L
--
Lasse Reichstein Nielsen - lrn@hotpop.com
Art D'HTML: <URL:http://www.infimum.dk/HTML/randomArtSplit. html>
'Faith without judgement merely degrades the spirit divine.'

[kaeli]

Re: Wildcards in javascript with SQL

In article <Urwgb.19519$4D .9872309@newsfe p2-win.server.ntli .net>,
ray.allison@vir gin.net enlightened us with...[color=blue]
>
> 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.
>[/color]

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]

Re: Wildcards in javascript with SQL

Ray Allison wrote:[color=blue]
>
> 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=ad oConnection.Exe cute(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.
>[/color]

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

--

Bryan Field-Elliot

http://netmeme.org

[Ray Allison]

Re: Wildcards in javascript with SQL

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

Again, Many thanks

Ray

"Lasse Reichstein Nielsen" <lrn@hotpop.com > wrote in message
news:r81pfhwc.f sf@hotpop.com.. .[color=blue]
> "Ray Allison" <ray.allison@vi rgin.net> writes:
>[color=green]
> > Any help and suggestions would be greatly appreciated.[/color]
>
> 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
>[/color]
<URL:http://epoch.cs.berkeley.edu:8000/se.../SQL_howto.htm
l>)[color=blue]
>
> /L
> --
> Lasse Reichstein Nielsen - lrn@hotpop.com
> Art D'HTML: <URL:http://www.infimum.dk/HTML/randomArtSplit. html>
> 'Faith without judgement merely degrades the spirit divine.'[/color]

[Ray Allison]

Re: Wildcards in javascript with SQL

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

Again, Many thanks,

Ray

"kaeli" <infinite.possi bilities@NOSPAM att.net> wrote in message
news:MPG.19ec6b 28b63e0b6a9898a a@nntp.lucent.c om...[color=blue]
> In article <Urwgb.19519$4D .9872309@newsfe p2-win.server.ntli .net>,
> ray.allison@vir gin.net enlightened us with...[color=green]
> >
> > For the Make to be an exact match is fine. However, it would be[/color][/color]
disireable[color=blue][color=green]
> > for the Model to display like items. But using an asterix as a wildcard
> > does not work.
> >[/color]
>
> 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
> -------------------------------------------------[/color]

[Ray Allison]

Re: Wildcards in javascript with SQL

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" <bryan@netmeme. org> wrote in message
news:OeAgb.5164 50$cF.185962@rw crnsc53...[color=blue]
> Ray Allison wrote:[color=green]
> >
> > The value given to mySQL is:
> > var mySQL="SELECT * FROM tblStock WHERE Make = ' " + varMake + " '[/color][/color]
AND[color=blue][color=green]
> > Model LIKE ' " + varModel + " ' ORDER BY PurchasePrice DESC";
> >
> > This is then executed by:
> > adoRecordset=ad oConnection.Exe cute(mySQL);
> >
> > For the Make to be an exact match is fine. However, it would be[/color][/color]
disireable[color=blue][color=green]
> > 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[/color][/color]
'abc3'[color=blue][color=green]
> > etc. but nothing is returned for 'abc* ' when we would expect all[/color][/color]
'abcnn' to[color=blue][color=green]
> > be returned. Note though that the asterix earlier in the SQL, 'Select[/color][/color]
* ',[color=blue][color=green]
> > does work by selecting all fields.
> >[/color]
>
> 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
>[/color]

[Steve van Dongen]

Re: Wildcards in javascript with SQL

On Wed, 8 Oct 2003 09:47:31 +0100, "Ray Allison"
<ray.allison@vi rgin.net> wrote:
[color=blue][color=green]
>> 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[/color]
>
>I hope the databases involved in our application are too insignificant for a
>would be attacker. But thanks for the advice on 'SQL injection'.[/color]

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