Hey all, these apostrophes are running loose in my data and it’s giving me errors when putting text into a SQLite database - is there a wrapper or some kind of way that I can install appstrophe-inclusive names / text into a SQLite database?
prepared statements are the key.
Thanks, looked it up and will try it. Thought those were only avail for MySQL.
Prepared Statements also protect from SQL Injection as well as allowing “special” characters such as single/double quotes
I’m using SQLite. I had my own wrapper for the functions I need so I could log any issues in the same place I log everything else. Extending that for prepared statements turned out to be simple enough - and necessary as I was starting to have the same issues as the OP.
it’s just a shame they don’t use the same syntax for different databases.
especially between sqlite and cubesql. i want to use prepared statement but have to cater for both since my application can be single user or multi user with just a extra file which i named xxlogin.rsd
ActiveRecord doesn’t care either since it’s using preparedStatements in the background and you don’t have to write any of the insert or update statements. Code is like this:
myRecord = New Data.User
myRecord.FirstName = "Bob"
myRecord.LastName = "O'Neil"
myRecord.save
I’ve done this also for my database libraries Bob, it’s just something you plan when you start a database library
not something you plan when you start sqlite and then go to mysql or postgres …
You should also include a Link to the What is ActiveRecord and ARGen
Sorry, I try not to pimp my products too much because I know it can be annoying. But since you asked:
ARGen the ActiveRecord generator. In freebie mode you’re limited to generating two tables at a time. Licensed lets you generate all the tables and starter UI you want. Work with desktop and web projects. For iOS only SQLite databases and no UI.
The situation is worse than you can imagine, if you don’t use Prepared Statements. Not only are there different SQL syntaxes, but the syntax varies inside the LIKE command too. I also use it to clean up JavaScript text!
As a result I created a method to clean up the best I could, catering to both general SQL commands and the LIKE command on each platform:
[code]Protected Function getSQLFormatWAD(SQLBrand As String, myFieldValue As String, LIKEcommand As Boolean = False) as String
'Convert string containing a single quotation or slash into escape codes
select case SQLBrand
case “DB2”, “PostgreSQL”, “Postgres”
myFieldValue = ReplaceAll(myFieldValue, “’”, “’’”) 'double the single quote
if LIKEcommand then
myFieldValue = ReplaceAll(myFieldValue, “”, “\”) 'this causes DOS paths in fields to not be recognised
myFieldValue = ReplaceAll(myFieldValue, “;”, “\;”) 'semi-colons may be used to put multiple statements on one line, so make it a literal
myFieldValue = ReplaceAll(myFieldValue, “%”, “\%”) 'percentages might muck up LIKE if they’re in the string being searched
myFieldValue = ReplaceAll(myFieldValue, “", "\”) 'underscore can act as a single character wild card in Postgres
end if
Return DefineEncoding(myFieldValue, Encodings.UTF8)
case “SQLite”, “CubeSQL”
myFieldValue = ReplaceAll(myFieldValue, “’”, “’’”) 'double the single quote
Return DefineEncoding(myFieldValue, Encodings.UTF8)
case “MySQL”
myFieldValue = ReplaceAll(myFieldValue, “’”, “\’”) ‘escape the single quote
if LIKEcommand then
myFieldValue = ReplaceAll(myFieldValue, “”, “\”)
myFieldValue = ReplaceAll(myFieldValue, "’", “\’”) 'escape the single quote
myFieldValue = ReplaceAll(myFieldValue, “;”, “\;”) 'semi-colons may be used to put multiple statements on one line, so make it a literal
End If
Return DefineEncoding(myFieldValue, Encodings.UTF8)
case “MSSQL”
myFieldValue = ReplaceAll(myFieldValue, “’”, “’’”) 'double the single quote
Return DefineEncoding(myFieldValue, Encodings.UTF8)
case “JavaScript”
'myFieldValue = ReplaceAll(myFieldValue, “?”, “”) 'Shift-Option-5 fails to import in HTML
'myFieldValue = ReplaceAll(myFieldValue, “?”, “”) 'Shift-Option-6 fails to import in HTML
‘myFieldValue = ReplaceAll(myFieldValue, “?”, “”) ‘Shift-Option-> fails to import in HTML
myFieldValue = ReplaceAll(myFieldValue, "’", "\’") 'escape the single quote
Return DefineEncoding(myFieldValue, Encodings.UTF8)
case else
myFieldValue = ReplaceAll(myFieldValue, “’”, “’’”) 'double the single quote
Return DefineEncoding(myFieldValue, Encodings.UTF8)
end select
Exception err
End Function
[/code]