newbee question on sql select

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

Re: newbee question on sql select

"Pepe" 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.

Re: newbee question on sql select

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.

Re: newbee question on sql select

"Aggro" wrote in message
news:zi3Pf.384$W03.81@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.

Re: newbee question on sql select

On 2006-03-06 17:52:03 -0500, "Bill Karwin" 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

Re: newbee question on sql select

On 2006-03-06 18:09:19 -0500, Aggro 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

Re: newbee question on sql select

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.