newbee question on sql select

[Pepe]

I use the following line to create a recordset. The s4, in the LIKE
statement, is text of a filepath and some of the files have an ( ' )
apostrophe in the file name. The code will not add those files to the
recordset. Is there a way to get around this without changing all the
filenames?

rs2 = App.eBookDB.SQLSelect("SELECT * FROM eBooks WHERE FilePath LIKE
'%" + s4 + "%'")

thanks, pepe

[Aggro]

Pepe wrote:

I use the following line to create a recordset. The s4, in the LIKE
statement, is text of a filepath and some of the files have an ( ' )
apostrophe in the file name. The code will not add those files to the
recordset. Is there a way to get around this without changing all the
filenames?

Escape ' characters with \.. So change

"that's it" to "that\'s it"

You will also need to escape \ characters with \. And also ? and %
characters unless you wish to use them like the way you are using % in
your example.

[Bill Karwin]

"Pepe" <no***@nowhere.com> wrote in message
news:2006030616555716807-noone@nowherecom...

I use the following line to create a recordset. The s4, in the LIKE
statement, is text of a filepath and some of the files have an ( ' )
apostrophe in the file name. The code will not add those files to the
recordset. Is there a way to get around this without changing all the
filenames?

rs2 = App.eBookDB.SQLSelect("SELECT * FROM eBooks WHERE FilePath LIKE '%"
+ s4 + "%'")

The safest way is to use parameterized queries. I can't tell from your
example which language or object library you're using, but any programming
interface should support parameters for queries.

So your query would be something like:

rs2 = App.eBookDB.SQLSelect("SELECT * FROM eBooks WHERE FilePath LIKE
CONCAT('%', ?, '%')")

And then you need to supply your s4 as the parameter, which assigns the
value of s4 to the ? parameter in the SQL statement. This avoids problems
with special characters in the value of s4, and it also helps to protect
against SQL injection security flaws.

The method for supplying the parameter depends on the language and object
library you're using. Refer to your documentation.

Regards,
Bill K.

[Bill Karwin]

"Aggro" <sp**********@yahoo.com> wrote in message
news:zi**************@read3.inet.fi...

And also ? and % characters unless you wish to use them like the way you
are using % in your example.

Do you mean the _ and % characters? The question mark is not a wildcard in
LIKE predicates; the underscore character is.

Regards,
Bill K.

[Pepe]

On 2006-03-06 17:52:03 -0500, "Bill Karwin" <bi**@karwin.com> said:

The safest way is to use parameterized queries. I can't tell from your
example which language or object library you're using, but any
programming interface should support parameters for queries.

So your query would be something like:

rs2 = App.eBookDB.SQLSelect("SELECT * FROM eBooks WHERE FilePath LIKE
CONCAT('%', ?, '%')")

And then you need to supply your s4 as the parameter, which assigns the
value of s4 to the ? parameter in the SQL statement. This avoids
problems with special characters in the value of s4, and it also helps
to protect against SQL injection security flaws.

The method for supplying the parameter depends on the language and
object library you're using. Refer to your documentation.

Bill K
I'm using RealBasic SQLDatabase, and I don't have any documentation for
it, only what I find on the net. I looked up CONCAT and understand what
it does but I don't know enough about the ? and how to use it.
pepe

[Pepe]

On 2006-03-06 18:09:19 -0500, Aggro <sp**********@yahoo.com> said:

Pepe wrote:

I use the following line to create a recordset. The s4, in the LIKE
statement, is text of a filepath and some of the files have an ( ' )
apostrophe in the file name. The code will not add those files to the
recordset. Is there a way to get around this without changing all the
filenames?

Escape ' characters with \.. So change

"that's it" to "that\'s it"

You will also need to escape \ characters with \. And also ? and %
characters unless you wish to use them like the way you are using % in
your example.

Thanks, I'll give it a try.
pepe

[Aggro]

Bill Karwin wrote:

Do you mean the _ and % characters? The question mark is not a wildcard in
LIKE predicates; the underscore character is.

That is right. Thank you for correcting this.