Do apostrophes and foot marks need to be treated specially in Sqlite

I have a program in which I have entered over 300 schools into a database. Now that I have got to the letter S (which of course includes St Bede’s, St Joseph’s, etc, I have started getting a syntax error. The error message ssays “near s”, so I can only presume it is the apostrophe that is causing the trouble.

But there is a small catch: If I add the name to the database without the apostrophe and then edit the entry and put the apostrophe in the name, the database accepts it.

This first code block is adding the entry:

[code] If schoolNameField.Text = “” Then
MsgBox(“A School Name is required!”)
Exit
ElseIf addressField.Text = “” Then
MsgBox(“An address is required.”)
Exit
ElseIf cityTownField.Text = “” Then
MsgBox(“A city or a town is required.”)
Exit
ElseIf postcodeField.Text = “” Then
MsgBox(“A postcode is required.”)
Exit
End If

Dim firstLetter, firstLetterNum As String

firstLetter = Left(schoolNameField.Text, 1) //returns First Letter of School Name
Select Case firstLetter
Case “A”
firstLetterNum = “1”
Case “B”
firstLetterNum = “2”
Case “C”
firstLetterNum = “3”
Case “D”
firstLetterNum = “4”
Case “E”
firstLetterNum = “5”
Case “F”
firstLetterNum = “6”
Case “G”
firstLetterNum = “7”
Case “H”
firstLetterNum = “8”
Case “I”
firstLetterNum = “9”
Case “J”
firstLetterNum = “10”
Case “K”
firstLetterNum = “11”
Case “L”
firstLetterNum = “12”
Case “M”
firstLetterNum = “13”
Case “N”
firstLetterNum = “14”
Case “O”
firstLetterNum = “15”
Case “P”
firstLetterNum = “16”
Case “Q”
firstLetterNum = “17”
Case “R”
firstLetterNum = “18”
Case “S”
firstLetterNum = “19”
Case “T”
firstLetterNum = “20”
Case “U”
firstLetterNum = “21”
Case “V”
firstLetterNum = “22”
Case “W”
firstLetterNum = “23”
Case “X”
firstLetterNum = “24”
Case “Y”
firstLetterNum = “25”
Case “Z”
firstLetterNum = “26”
End Select

invoiceDatabase.SQLExecute(“BEGIN TRANSACTION”)
invoiceDatabase.SQLExecute ("INSERT INTO client (name,address,suburb,city_town,postcode,telephone,email,additional_comments,startAlpha) "_

  • “VALUES ( '” + schoolNameField.Text + “’,’” + addressField.Text + “’,’” + suburbField.Text + “’,’” + cityTownField.Text + “’,’” + postcodeField.Text + “’,’” + phoneField.Text + “’,’” + emailField.Text + “’,’” + detailsField.Text + “’,’” + firstLetterNum + “’)” )

Dim data As RecordSet

'Get last ClientID
data = InvoiceDatabase.SQLSelect(“SELECT client_id FROM client”)
If data <> Nil Then
While Not data.EOF
DatabaseModule.lastClient = data.Field(“client_id”).IntegerValue
data.MoveNext
Wend
End if
data.Close

Dim i, contactCount As Integer

'Get number of contacts
contactCount = Customer.contacts.Ubound

If contactCount > -1 Then
For i = 0 to contactCount
InvoiceDatabase.SQLExecute ("INSERT INTO contact (contact_type_id,contact_name,contact_telephone,contact_email,contact_other) "_
+ “VALUES (’” + Customer.contacts(i).getContact(“type”) + “’,’” + Customer.contacts(i).getContact(“name”) + “’,’” + Customer.contacts(i).getContact(“telephone”) + “’,’” + Customer.contacts(i).getContact(“email”) + “’,’” + Customer.contacts(i).getContact(“other”) + “’)”)
Next
End if

'Get last contactID
data = InvoiceDatabase.SQLSelect(“SELECT contact_id FROM contact”)
If data <> Nil Then
While Not data.EOF
DatabaseModule.lastContact = data.Field(“contact_id”).IntegerValue
data.MoveNext
Wend
End if
data.Close

Dim contactNum As Integer

If contactCount > -1 Then
contactNum = DatabaseModule.lastContact

For i = contactNum - contactCount to contactNum
  InvoiceDatabase.SQLExecute("INSERT INTO client_contact (client_id,contact_id) VALUES (" + Str(DatabaseModule.lastClient) + "," + Str(i) + ")")
Next

End If

ReDim Customer.contacts(-1)
NoContactsField.Text = “”

Dim quant As Integer
For i = 0 to 7
If qtyText(i).Enabled Then
quant = Val(qtyText(i).Text)
InvoiceDatabase.SQLExecute(“INSERT INTO standing_orders (client_id, standOrder_type, standOrder_qty) VALUES (” + Str(DatabaseModule.lastClient) + “,” + Str(qtyText(i).Index) + “,” + Str(quant) + “)”)
End If
Next
If invoiceDatabase.Error Then
MsgBox("Error: " + invoiceDatabase.ErrorMessage)
invoiceDatabase.Rollback
Else
invoiceDatabase.Commit
End If
Self.Close
invoiceWindow.Show[/code]

and this is editing the entry:

[code] //Update the data in the database
If schoolNameField.Text = “” Then
MsgBox(“A School Name is required!”)
Exit
ElseIf addressField.Text = “” Then
MsgBox(“An address is required.”)
Exit
ElseIf cityTownField.Text = “” Then
MsgBox(“A city or a town is required.”)
Exit
ElseIf postcodeField.Text = “” Then
MsgBox(“A postcode is required.”)
End If

Dim clientID As Integer
clientID = Customer.getCustSelection()

InvoiceDatabase.SQLExecute(“BEGIN TRANSACTION”)
InvoiceDatabase.SQLExecute(“UPDATE client SET name = '” + schoolNameField.Text + “’, address = '” + addressField.Text + “’, suburb = '” + suburbField.Text + “’, city_town = '” + cityTownField.Text + “’, postcode = '” + postcodeField.Text + “’, telephone = '” + phoneField.Text + “’, email = '” + emailField.Text + “’, additional_comments = '” + detailsField.Text + "’ WHERE client_id = " + Str(clientID))

//When editing customer details firstly delete all Standing Orders for this customer (any deselected have already been deleted …
InvoiceDatabase.SQLExecute("DELETE FROM standing_orders WHERE client_id = " + Str(Customer.getCustSelection) + “”)

// … then add the Standing Order (both the changed and the same).
Dim quant As Integer
Dim i As Integer
For i = 0 to 7
If qtyText(i).Enabled Then
quant = Val(qtyText(i).Text)
InvoiceDatabase.SQLExecute(“INSERT INTO standing_orders (client_id, standOrder_type, standOrder_qty) VALUES (” + Str(Customer.getCustSelection) + “,” + Str(qtyText(i).Index) + “,” + Str(quant) + “)”)
If InvoiceDatabase.Error Then
InvoiceDatabase.Rollback
MsgBox(InvoiceDatabase.ErrorMessage)
Else
InvoiceDatabase.Commit
//Changed the data
End If
End If
Next

InvoiceWindow.Show
Self.Close[/code]

They are much the same, except for the database commands.

Edit: I am creating this app on a Mac, but it is destined for Windows.

Hi Cliff

If you change to using prepared statements these problems will be catered for. Otherwise you’ll need to “Double Quote” the data using replaceall. You should always use prepared statements to avoid sql injection issues where you are using user entered data.

You can also get the first letter number using the AscB function e.g. firstletternum = Str(AscB(UpperCase(Left(schoolNameField.Text, 1))) - 64)

HTH
Wayne

Thanks for your help, Wayne.

I have looked at prepared statements and they seem straight forward enough. I will change all my interaction with the database to them.

I did know they prevented sql injection, but as this app is not destined for the web, I assumed it didn’t need prepared statements. But now that you mention them, I guess even a desktop app can have sql injection (my mind was locked into the web scenario).

I also meant to say thanks for the AscB function. It sure takes a while to figure out what functions are available and what to use :slight_smile:

I echo Wayne’s advice. As a convenience, you can also use a DatabaseRecord for your inserts or edit the RecordSet for updates. Either of these will work the same as using a prepared statement.