PureBasic Forums - English

I'm having a problem with titles that have an apostrophe in it when running a query on my SQLite3 database.

This is the query: If the title is "Uncle Johns Roadhouse" it will find it with no problem. If the title is "Uncle John's Roadhouse" it will not find it.

Is there a way to either ignore the apostrophe or some other way to make the query find records with an apostrophe in the title?

Thanks

Hi,

you have to replace all ' with 2 '

Thanks infratec. When you say "you have to replace all ' with 2 '" are you referring to "SELECT * FROM...."?

If that is what you mean, I need to find all records with that title in the database.

Thanks infratec. I realized what you were saying after I looked at your response again. Your example works perfectly!

Thanks you very much.

Or... Assuming you do not have to escape #DQ$("), if they are not in the field you are searching?

Someone taught me (Spikey I think? Or even Infratec?? I'm not too old to learn) to avoid the whole apostrophe issue is to paramaterise all variables passed to statements and queries

SetDatabaseString(Databasehandle.i, 0, "Uncle John's Roadhouse")

Databasehandle is the handle to your opened database.
0, is the first variable index you want to paramaterise.
And of course, "Uncle John's Roadhouse" is the problem string.

Then you just pass the query like this:

result = DatabaseQuery(#dbaseID,"SELECT * FROM title WHERE title = ?"

You can have more than one 'prepared' variable this this way, just increase the index
Someone else mentioned to me that you cannot pass table names like that but then you would not name tables like that, only strings and by the look of your query, TableName isn't actually a table's name.

Yes, prepared statements are limited. The variable(s) cannot be a Table or Column name, only contents of the Columns for insert or select queries.

Code: Select all

result = DatabaseQuery(#dbaseID,"SELECT * FROM  title WHERE  title = '" + tablename + "'")

Fangbeast is right: tablename is between apostrophes in this query, so is treated as a string and not as a variable, so SetDataBaseString() must work and will handle apostrophes and even quotes without problems.

Marc56us wrote:

Code: Select all

result = DatabaseQuery(#dbaseID,"SELECT * FROM  title WHERE  title = '" + tablename + "'")

Fangbeast is right: tablename is between apostrophes in this query, so is treated as a string and not as a variable, so SetDataBaseString() must work and will handle apostrophes and even quotes without problems.

He's made a mistake though I think. Why would you try to check if a title is the same as a tablename? I don't think he intended to do that somehow.

More likely he was searching for a title in a table. Just guessing.

I like this
viewtopic.php?f=12&t=32026

Binding variables in sql statement is preferred way avoiding common sql injection.

literal strings feature is convenient too.