It is currently Wed Apr 24, 2019 5:03 pm

All times are UTC + 1 hour




Post new topic Reply to topic  [ 12 posts ] 
Author Message
 Post subject: Ignoring apostrophes in MySQLite3 queries
PostPosted: Fri Apr 12, 2019 4:22 pm 
Offline
User
User

Joined: Wed Sep 10, 2014 7:17 am
Posts: 86
Location: Ontario Canada
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:
 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

_________________
Code In Basic - http://www.codeinbasic.com


Top
 Profile  
Reply with quote  
 Post subject: Re: Ignoring apostrophes in MySQLite3 queries
PostPosted: Fri Apr 12, 2019 4:47 pm 
Offline
Addict
Addict

Joined: Sun Sep 07, 2008 12:45 pm
Posts: 4163
Location: Germany
Hi,

you have to replace all ' with 2 '
Code:
text$ = ReplaceString(text$, "'", "''")


Top
 Profile  
Reply with quote  
 Post subject: Re: Ignoring apostrophes in MySQLite3 queries
PostPosted: Fri Apr 12, 2019 5:01 pm 
Offline
User
User

Joined: Wed Sep 10, 2014 7:17 am
Posts: 86
Location: Ontario Canada
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.

_________________
Code In Basic - http://www.codeinbasic.com


Top
 Profile  
Reply with quote  
 Post subject: Re: Ignoring apostrophes in MySQLite3 queries
PostPosted: Fri Apr 12, 2019 5:19 pm 
Offline
Addict
Addict

Joined: Sun Sep 07, 2008 12:45 pm
Posts: 4163
Location: Germany
Code:
tablename = ReplaceString(tablename, "'", "''")
result = DatabaseQuery(#dbaseID,"SELECT * FROM  title WHERE  title = '" + tablename + "'")


Top
 Profile  
Reply with quote  
 Post subject: Re: Ignoring apostrophes in MySQLite3 queries
PostPosted: Fri Apr 12, 2019 5:24 pm 
Offline
User
User

Joined: Wed Sep 10, 2014 7:17 am
Posts: 86
Location: Ontario Canada
Thanks infratec. I realized what you were saying after I looked at your response again. Your example works perfectly!

Thanks you very much.

_________________
Code In Basic - http://www.codeinbasic.com


Top
 Profile  
Reply with quote  
 Post subject: Re: Ignoring apostrophes in MySQLite3 queries
PostPosted: Fri Apr 12, 2019 6:29 pm 
Offline
Addict
Addict
User avatar

Joined: Wed Dec 23, 2009 10:14 pm
Posts: 2907
Location: Boston, MA
Or...
Code:
#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?

_________________
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum


Top
 Profile  
Reply with quote  
 Post subject: Re: Ignoring apostrophes in MySQLite3 queries
PostPosted: Fri Apr 12, 2019 9:32 pm 
Offline
PureBasic Protozoa
PureBasic Protozoa
User avatar

Joined: Fri Apr 25, 2003 3:08 pm
Posts: 4398
Location: Not Sydney!!! (Bad water, no goats)
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.

_________________
Amateur Radio, D-STAR/VK3HAF


Top
 Profile  
Reply with quote  
 Post subject: Re: Ignoring apostrophes in MySQLite3 queries
PostPosted: Fri Apr 12, 2019 10:11 pm 
Offline
Addict
Addict
User avatar

Joined: Wed Dec 23, 2009 10:14 pm
Posts: 2907
Location: Boston, MA
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.

_________________
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum


Top
 Profile  
Reply with quote  
 Post subject: Re: Ignoring apostrophes in MySQLite3 queries
PostPosted: Sat Apr 13, 2019 6:56 am 
Offline
Enthusiast
Enthusiast

Joined: Sat Feb 08, 2014 3:26 pm
Posts: 579
Location: France
Quote:
Code:
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.

:wink:

_________________
(English is not my native language, I use an online translator)
http://mdacme.com


Top
 Profile  
Reply with quote  
 Post subject: Re: Ignoring apostrophes in MySQLite3 queries
PostPosted: Sat Apr 13, 2019 7:52 am 
Offline
PureBasic Protozoa
PureBasic Protozoa
User avatar

Joined: Fri Apr 25, 2003 3:08 pm
Posts: 4398
Location: Not Sydney!!! (Bad water, no goats)
Marc56us wrote:
Quote:
Code:
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.

:wink:


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.

_________________
Amateur Radio, D-STAR/VK3HAF


Top
 Profile  
Reply with quote  
 Post subject: Re: Ignoring apostrophes in MySQLite3 queries
PostPosted: Sat Apr 13, 2019 11:42 am 
Offline
Addict
Addict
User avatar

Joined: Fri May 12, 2006 6:51 pm
Posts: 1677
Location: Germany
I like this
viewtopic.php?f=12&t=32026

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

_________________
My Projects ThreadToGUI / OOP-BaseClass / OOP-BaseClassDispatch / Event-Designer
PB v3.30 / v5.70 - OS Mac Mini OSX 10.xx - VM Window Pro / Linux Ubuntu
Downloads on my Webspace


Top
 Profile  
Reply with quote  
 Post subject: Re: Ignoring apostrophes in MySQLite3 queries
PostPosted: Sat Apr 13, 2019 12:42 pm 
Offline
New User
New User

Joined: Wed Aug 23, 2017 11:16 am
Posts: 5
Binding variables in sql statement is preferred way avoiding common sql injection.

literal strings feature is convenient too.

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


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 12 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: Exabot [Bot] and 5 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye