how to escape single quotes for sqlite input?

Getting Started
1

Like the title says, I’m trying to figure out how to escape single quotes in my SQLite input data.
I’m using the text from a text field named “heading”, so obviously the data would be grabbed from heading.text. I want to be able to allow single quotes in the text, and input to the DB works fine, but I get an error on DB output, caused by the single quote in the text. I’ve looked at SQLitePreparedStatement, but I don’t understand that at all…may as well be written in Greek. :wink:
Could some kind soul please help me out with this?

2

SQLitePreparedStatement is the best way to go. The benefits are much more than escaping single quotes.

Can you post some code where you’ve tried using a prepared statement? Maybe we can help you understand them a little better.

3

the simple answer to the quote problem is to replace any instance of single quote by two such quotes.
t = replaceall(myvalue, “’”,"’’")

Prepared statements allow you to set the value of what is more or less a variable, with the string, which can contain quotes and other ‘dangerous’ characters.
Generally agreed to be the best way to handle the issue.

4

Read the example on the SQLitePreparedStatement page in the docs.

Instead of concatenating the SQL string like that:

"SELECT * FROM Persons WHERE Name = '" + nameVar + "' AND Age >= " + 30 + ";"

and thereby worrying about quotes, SQL injections, and more, you create the SQL statement with placeholders (question mark):

"SELECT * FROM Persons WHERE Name = ? AND Age >= ?"

Then you tell the PreparedStatement object (called ps in that case), of what data type each placeholder is:

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT) // Name is a string ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER) // Age is an integer
Then you execute the SQL statement by handing over the appropriate values, in this case a string for the name followed by an integer for the age:

Dim rs As RecordSet = ps.SQLSelect("John", 20)
5

I have found that there are two levels of escaping you need with SQL brands:

  1. when assigning text to a variable within single quotes (as above, e.g. singe quotes)
  2. when searching for text within the LIKE command. In this instance special characters may need to be escaped e.g. \, % and _ (backslash, percent and underscore).

I know the first one affects SQLite, but I’m not sure if the second one also needs to be accounted for.

6

Thanks one and all.

Eli Ott:
I did read the example on the SQLitePreparedStatement page in the docs…I must be dim witted, because I can’t make head nor tail of it…your explanation helps a bit though.
I’m confused as to how this would apply to an sql insert statement, since my values are all named variables, and I’m not returning a recordset.

7

It works the same. By the way, one of the examples in the docs is with an INSERT statement.

8

The insert version works much the same as the select version, but you call SQLExecute rather than SQLSelect

[code]
db.SQLExecute(“CREATE TABLE Persons(Name, Age)”)

Dim ps As PreparedSQLStatement = _
db.Prepare(“INSERT INTO Persons (Name, Age) VALUES (?, ?)”)

ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_LONG)

ps.SQLExecute(“john”, 20)
ps.SQLExecute(“john”, 21)
//now commit the changes

db.SQLExecute(“COMMIT”)[/code]

9

Thanks, Eli, I see it. I’ll give it a go and let you know how I do.

10

Here’s what I’ve got…doesn’t work. No data gets input to the DB. What am I missing…any thoughts?

[code] Dim ps As SQLitePreparedStatement = _
db.Prepare(“INSERT INTO data (id,date,heading,refs,notes) VALUES (?, ?, ?, ?, ?)”)

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)

ps.SQLExecute("",entrydate.text, heading.text, refs.text, notes.text)
db.SQLExecute("COMMIT")[/code]
11

[code]Dim ps As SQLitePreparedStatement = _
db.Prepare(“INSERT INTO data (date,heading,refs,notes) VALUES (?, ?, ?, ?)”) // Remove “id” !!!

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)

ps.SQLExecute("", entrydate.text, heading.text, refs.text, notes.text)
// Make sure that entrydate.text is a string which is recognized as date string by SQLite (unless the column named date in you database is a text field)

If db.Error Then
db.Rollback()
Else
db.Commit()
End[/code]

12

Thanks a million, Eli! I made one small adjustment to your last code:

ps.SQLExecute("", entrydate.text, heading.text, refs.text, notes.text)

I removed the “” and now everything works pefectly! Thank you so much for your help! :slight_smile:

13

I do this instead as I(me) find it easier to read.

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, entrydate.text)
ps.Bind(0, heading.text)
ps.Bind(0, refs.text)
ps.Bind(0, notes.text)

ps.SQLExecute()
14

Edit:

[code]ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, entrydate.text)
ps.Bind(1, heading.text)
ps.Bind(2, refs.text)
ps.Bind(3, notes.text)

ps.SQLExecute()[/code]

15

While I prefer to give the params in the ps.SQLExecute statement, both are legitimate and Albin’s preference is the only choice if you need to supply the parameters in a loop. For example, if you have created a generic method that takes the parameters as an array.