Web pagewild card query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all!

A easy question for all you experts out thier. I have a simple database that
I'm desighning a data web page for, and I would like to drop a field with a
wild card query that contains >> Like [enter search] & "*"<< without the
greater and lesser signs of course. Any how this will not work unless I
remove & "*" . Is there any other way to make a wild card query in a data
access web page?

Thanks for your time

Bob
 
Bob said:
Hello all!

A easy question for all you experts out thier. I have a simple
database that I'm desighning a data web page for, and I would like to
drop a field with a wild card query that contains >> Like [enter
search] & "*"<< without the greater and lesser signs of course. Any
how this will not work unless I remove & "*" . Is there any other way
to make a wild card query in a data access web page?

Thanks for your time
Click to expand...

Are you using ADO objects to run this query, by any chance? That's what
I'd expect for a web page. When you use ADO, the wild card character
equivalent to the asterisk (*) is the percent sign (%). So use this for
your query:

Like [enter search] & "%"

If you want to build a stored query that will work both when opened from
Access and from the web page, use the ADO wild-card character with the
ALike SQL operator:

ALike [enter search] & "%"
 
A easy question for all you experts out thier. I have a simple database
that
I'm desighning a data web page for, and I would like to drop a field with
a
wild card query that contains >> Like [enter search] & "*"<< without the
greater and lesser signs of course. Any how this will not work unless I
remove & "*" . Is there any other way to make a wild card query in a data
access web page?
Click to expand...

To answer your question:

1. Get the value from the TextBox:

Dim sSearch As String
sSearch = txtSearch.value

2. Embed it into your SQL Statement
sql = "SELECT...FROM...WHERE...."
sql = sql & "LIKE '" & sSearch & "*'"

Note the contents of the sql string by pasting the above line into a
notepad. LIKE needs to say something like this: LIKE 'SomePattern'

So the LIKE expression has to have either apostrophe's or double-quotes
around the sSearch expression.

=========
RED FLAG:
=========

You must use extreme caution when using this technique. Let's say I'm doing
this on the Northwind database that ships with Access, and I want to use it
to get the ALFKI customer from the Customers table:

User types ALFKI into the textbox.
SQL becomes: SELECT * FROM Customers WHERE CustomerID LIKE 'ALFKI*'

So far so good. Now watch this. User puts THIS in the textbox:

ALFKI*'; DELETE * FROM [Order Details]; DELETE * FROM Orders; SELECT * FROM
Customers WHERE Customer LIKE 'ALFKI

Then here is what your VBA code or expression builds from the entered text:

SELECT * FROM Customers WHERE CustomerID LIKE 'ALFKI*'; DELETE * FROM [Order
Details]; DELETE * FROM Orders; SELECT * FROM Customers WHERE Customer LIKE
'ALFKI*'

Oops.

This is called an SQL Injection Attack. Any time you take user input and
directly insert it into a string of SQL, you have a possibility of a
disaster like that happening. Your code must either (a) avoid this or (b)
carefully examine the user input to make sure it contains no SQL Syntax.

Typically, this prevention is done using parameters. But the LIKE operator
gets cranky when you try to feed it a parameter, so make sure you test this
to death before you let a user touch it.

Perhaps someone else in here can help me out by supplying a link to a good
article.
--


Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei
 
Like [enter search] & "%"

DOH! I forgot to mention % instead of *. But please still be careful of
SQL Injection attacks.
--


Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei
 
DOH! I forgot to mention % instead of *. But please still be
That is an excellent warning. Thank you for giving it.
Click to expand...

No kidding. :) I'll give you one guess how I know. (insert profanity
here)
--


Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei
 
Thanks All

The simple % works fine

Thanks again!

Mike Labosh said:
A easy question for all you experts out thier. I have a simple database
that
I'm desighning a data web page for, and I would like to drop a field with
a
wild card query that contains >> Like [enter search] & "*"<< without the
greater and lesser signs of course. Any how this will not work unless I
remove & "*" . Is there any other way to make a wild card query in a data
access web page?
Click to expand...

To answer your question:

1. Get the value from the TextBox:

Dim sSearch As String
sSearch = txtSearch.value

2. Embed it into your SQL Statement
sql = "SELECT...FROM...WHERE...."
sql = sql & "LIKE '" & sSearch & "*'"

Note the contents of the sql string by pasting the above line into a
notepad. LIKE needs to say something like this: LIKE 'SomePattern'

So the LIKE expression has to have either apostrophe's or double-quotes
around the sSearch expression.

=========
RED FLAG:
=========

You must use extreme caution when using this technique. Let's say I'm doing
this on the Northwind database that ships with Access, and I want to use it
to get the ALFKI customer from the Customers table:

User types ALFKI into the textbox.
SQL becomes: SELECT * FROM Customers WHERE CustomerID LIKE 'ALFKI*'

So far so good. Now watch this. User puts THIS in the textbox:

ALFKI*'; DELETE * FROM [Order Details]; DELETE * FROM Orders; SELECT * FROM
Customers WHERE Customer LIKE 'ALFKI

Then here is what your VBA code or expression builds from the entered text:

SELECT * FROM Customers WHERE CustomerID LIKE 'ALFKI*'; DELETE * FROM [Order
Details]; DELETE * FROM Orders; SELECT * FROM Customers WHERE Customer LIKE
'ALFKI*'

Oops.

This is called an SQL Injection Attack. Any time you take user input and
directly insert it into a string of SQL, you have a possibility of a
disaster like that happening. Your code must either (a) avoid this or (b)
carefully examine the user input to make sure it contains no SQL Syntax.

Typically, this prevention is done using parameters. But the LIKE operator
gets cranky when you try to feed it a parameter, so make sure you test this
to death before you let a user touch it.

Perhaps someone else in here can help me out by supplying a link to a good
article.
--


Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Wild Cards 5
Wil Cards in Report Preview Command Where Condition 1
What is the wild card for an Access query (MS query) parameter? 2
Query Help 4
How do I write this: WHERE word LIKE '*' in ADO? 2
IIF with AND and OR operator along with LIKE condition 7
like[]* 2
Wild card not working in Access 2010 query 4

Back
Top