Punctuation Problem

Databases
1

I’m writing an iOS App that use extensive text input/output from text fields and text areas into sqlite databases. No problem as long as some punctuation rules are ignored. The App crashes if the text contains such punctuation as apostrophes, commas, (brackets) and the like. Seems to be OK in Text Area but not Text Field. Any suggestions?

2

Use PreparedStatements to write to the DB.

3

Tim got there 2 seconds before I did

4

Whoa, you guys are quick. Thanks. I’ll look up PreparedStatements. That’s a new one for me.

5

Also research “SQL injection” for why Prepared Statements are mandatory when you do not control all the input into a SQL statement.

6

For example, suppose you have simple SQL that does this:

sql = "SELECT * FROM person WHERE first_name = '" + firstName + "'"

What happens when a mischievous user enters this for firstName?

mike' ; DROP TABLE person ; SELECT 'Sanitize your inputs!

(Someone will post “Bobby Tables” soon, I’m sure.)

Edit: Fixed for Norman. :slight_smile:

7

Looking at this in the Language Reference, it seems I should be using this

Dim ps As SQLitePreparedStatement = _
  db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?")

ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)

ps.Bind(0, "john")
ps.Bind(1, 20)

Dim rs As RecordSet = ps.SQLSelect
If rs <> Nil Then
  While Not rs.EOF
    MsgBox("Name: " + rs.Field("Name").StringValue + _
      " Age: " + rs.Field("Age").StringValue)
    rs.MoveNext
  Wend

instead of this (my code for this particular DB)

[code]
Dim dbFile As FolderItem
dbFile = SpecialFolder.Documents.Child(“MaintLog.sqlite”)

	If dbFile.Exists Then
			App.MaintLogDB= New iOSSQLiteDatabase
			App.MaintLogDB.DatabaseFile = dbFile
			
			If App.MaintLogDB.Connect Then
					ID=Array("Connect")
					DateData=Array("Connect")
					EngHoursData=Array("Connect")
					MaintenanceData=Array("Connect")
					ScrollRef=Array("0")
					Status=Array("Off")
			End If
	Else
			//Do Nothing
	End If
	
	Dim sql As Text
	sql = "Select * From MaintLog Order By Date1 Desc"
	Dim data As iOSSQLiteRecordSet
	
	data = App.MaintLogDB.SQLSelect(sql)
	
	While Not data.EOF
			
			ID.Append(data.Field("ID").IntegerValue.ToText)
			
			MaintenanceData.Append(data.Field("Type").TextValue)
			
			Dim d As Date
			Dim dt As Text
			d=(data.Field("Date1").DateValue)
			dt=d.Month.ToText+"/"+d.Day.ToText+"/"+d.Year.ToText.Right(2)
			
			DateData.Append(dt)
			
			EngHoursData.Append(data.Field("EngineHours").
			
			EngHoursData.Append(data.Field("EngineHours").TextValue)
			NewHours2=(data.Field("EngineHours").TextValue)
			
			ScrollRef.Append(i.ToText)
			
			Status.Append(data.Field("Status").TextValue)
			
			data.MoveNext
			
			i=i+1
	Wend
	data.close[/code]

Am I on the right track Gentlemen?

8

Right track, but with iOS you’ll need the new framework equivalent.

9

Ouch!
That’s getting beyond my current expertise.

Thanks Kem

Sorry about the double post.

10

[quote=334277:@Kem Tekinay]
(Someone will post “Johnny Tables” soon, I’m sure.)[/quote]
Bobby dammit ! :stuck_out_tongue:
https://xkcd.com/327/

11

Yup, That says it all.

If it’s OK to put all those punctuations in a Text Area, why not a Text Field?

ASCII?

There’s gotta be a workaround.

12

[quote=334285:@Werner Hamp]Yup, That says it all.

If it’s OK to put all those punctuations in a Text Area, why not a Text Field?

ASCII?

There’s gotta be a workaround.[/quote]
Who said this? TextArea and TextField can accept the same type of output, TextArea is just capable of multiline and styled text

13

My guess is that the Text Area is curling your single-quotes.

14

I’m going by empirical evidence Dave. I can put all kinds of crap into the Text Area with impunity but an apostrophe in the the Text Field crashes the App. Same Database, different Data Field. Maybe I can pre format the input somehow. I’d change the the Text Fields to Text Areas but then that pesky keyboard won’t go away at the appropriate time besides, I’d have to change over at least a hundred or more.

After 5 months working on this App, I’m about burned out but close to the finish line.

15

P.S. My potential customers are of the age that still use punctuation.

16

Sorry… I forgot you were talking about Xojo for iOS…

17

All the responses so far are about protecting your db from SQL injection, but that shouldn’t cause the app to crash. Are you getting any error messages? When does the crash occur? When saving the data to the db? When loading the db into the fields? Or at other places in the program? Are you checking for errors at the appropriate places?

As to SQL injection protection, it may be simpler to create a function to sanitize your fields:

Function SafeSQL(extends sqlString as String) as String // Double up all single quotes and return the string enclosed in single quotes Return "'" + ReplaceAll(sqlString, "'", "''") + "'" End Function

Then using Kem’s example it would be

sql = "SELECT * FROM person WHERE first_name = " + firstName.SafeSQL
18

Or just use a prepared statement (for insert select delete etc) and be done with it

19

I’m sorry Jay, but that’s bad advice. Do not attempt to sanitize SQL yourself, always use prepared statements. That’s exactly what they are there for.

20

I was suggesting this as a less invasive change for the OP, as he seemed to indicate having to change all his SQL code would be a lot of work. Adding my function could be accomplished easily with selective search/replace on .Text.