Ignoring apostrophes in MySQLite3 queries

[Columbo]

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:

Code: Select all

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

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

[infratec]

Hi,

you have to replace all ' with 2 '

Code: Select all

text$ = ReplaceString(text$, "'", "''")

[Columbo]

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.

[infratec]

Code: Select all

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

[Columbo]

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

Thanks you very much.

[skywalk]

Or...

Code: Select all

#DQ$ = Chr(34)
result = DatabaseQuery(#dbaseID,"SELECT * FROM  title WHERE  title = " + #DQ$ + tablename + #DQ$

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

[Fangbeast]

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.

[skywalk]

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.

[Marc56us]

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.

[Fangbeast]

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.

[mk-soft]

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

Code: Select all

titlename.s = "MyTitle" 
SQL.s = Format("Select * FROM  title WHERE  title = \'%s\'", @titlename)
Debug SQL

[pbfast]

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

literal strings feature is convenient too.

Code: Select all

title.s = "Uncle John's Roadhouse"
debug ~"Select * FROM  title WHERE  title = \"" + title + ~"\""