Text Field dislikes Apostrophes??

Hi,
I have a text field in my window, and the content gets saved to my database with no problems.

I have now noticed however, that if I use a hyphen in the text field and then try to save it to the database - it works as expected (or at least I do NOT see any error messages) until I try to populate a text area with the entry - Then I get an error message saying there is no record found?

The only code I have attached to the text field is a declare in the open event, which creates a rounded text field:

declare sub setBezelStyle lib "Cocoa" selector "setBezelStyle:" ( handle as integer, value as integer ) setBezelStyle( me.handle, 1 )

The code I use to save to the database is below:

[code] // UPDATE THE DATABASE IF BOTH TEXT FIELDS HAVE DATA
If TitleField.text <> “” And CodeField.text <> “” Then
rs.Edit
rs.field(“Title”).StringValue=TitleField.text
rs.field(“Code”).StringValue=CodeField.Text
rs.Update

// CHECK FOR DATABASE ERROR
If db.Error Then
  MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
  Return
  
Else
  
  // COMMIT THE CHANGES
  db.commit
  
  // UPDATE THE LISTBOX
  MainWindow.Timer2.mode = MainWindow.Timer2.ModeSingle
  
  // CLOSE THE WINDOW
  Self.Close
  
end if

End If[/code]

The code I use to display the entry in a text area is as follows:

[code] // POPULATE THE CODETEXTAREA
dim sql, selected as string
selected=Listbox1.cell(Listbox1.listindex,0)
sql=“select SRef, Title, Code from Snippets where Title=’”+selected+"’"
rs=db.SQLSelect(sql)

if rs=nil then
msgbox(“No record found.”)
self.close

else
dim s as string = DefineEncoding( rs.field(“Code”).StringValue , Encodings.UTF8 )
CodeTextArea.text=s

end if
[/code]

Does anyone have any ideas?

Debug.
Check the db.Error after your db.SQLSelect(sql)

Another thing to avoid is trusting on texts as keys for your records. Have 3 lines with “Apples” and you can’t know which one was that last one you changed. DB 101, have unique record IDs for any record, that’s why every DB server have some kind of auto-increment unique id generator feature.

I am getting a no record found error - so rs must = Nil - but I have no idea why, as it works perfectly until an apostrophe is inserted.

rs=Nil only occurs if an apostrophe is inserted?

I have also just noticed that this DOES NOT HAPPEN in a text area, ONLY in a text field?

Your data isn’t getting inserted or updated as you expect
Not exactly sure why
But you are trying to commit when it appears you’ve not started a transaction

 // UPDATE THE DATABASE IF BOTH TEXT FIELDS HAVE DATA
  If TitleField.text <> "" And CodeField.text <> "" Then
    rs.Edit
    rs.field("Title").StringValue=TitleField.text
    rs.field("Code").StringValue=CodeField.Text
    rs.Update
    
    // CHECK FOR DATABASE ERROR
    If db.Error Then
      MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
      Return
      
    Else
      
      // COMMIT THE CHANGES
      db.commit
      ////////////// <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 
      // believe it or not you can get a DB error here since I suspect there's no 
      // transaction in progress so your data isn't saved
      ////////////// <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 

      // UPDATE THE LISTBOX
      MainWindow.Timer2.mode = MainWindow.Timer2.ModeSingle
      
      // CLOSE THE WINDOW
      Self.Close
      
    end if
    
  End If

The bit I do not understand is that without an apostrophe - it gets saved fine.
I can close my app and then re-open it, so the data definitely gets saved to the database (I have looked).

BUT if I use an apostrophe - you are saying it DOES NOT get saved?

Now I am totally perplexed :frowning:

Strings in SQL are in quotes, so a quote within a string has to be doubled:

"abc's def" // Xojo string "'abc''s def'" // Xojo string for use in SQL

I can swear that when I wrote my answer the title were “Text Field dislikes hyphens??”, Eliott is correct, you are breaking the SQL syntax with the apostrophes. Interestingly you should be receiving some error related to some “Bad SQL syntax”.

x = “aaa’ _ garbage”
sql=“Select SRef, Title, Code from Snippets where Title=’”+ x +"’"

SQL -> Select SRef, Title, Code from Snippets where Title=‘aaa’ _ garbage’

Eli / Rick,
I am a bit lost now, so I have 2 questions:

  1. How would I then automatically surround any quotes (either " or ') which the user enters, before saving?

  2. Why does a text area work correctly, but not a text field - if it uses the same code to save to the database?

Thank you both for the help :slight_smile:

UPDATE - could I just change the database column type to blob, as that is what I use for a text area which does not have this problem.

When not using direct SQL (fields → edit/update…) XOJO is taking care of this part for you.
Your problem resides when you try to write the code by yourself like I exemplified.
The “dirt easy not recommended way” is string substitution like Eli said.
The recommended way is using Prepared Statements.
Do some research about it please, XOJO is not DB agnostic for this part. http://documentation.xojo.com/index.php/PreparedSQLStatement

Rick,
I have looked at the Prepared Statements page you recommended, but that is a bit beyond my capabilities at the moment.
I will however, try.

Am I correct in thinking that the top part of my code:

// UPDATE THE DATABASE IF BOTH TEXT FIELDS HAVE DATA If TitleField.text <> "" And CodeField.text <> "" Then rs.Edit rs.field("Title").StringValue=TitleField.text rs.field("Code").StringValue=CodeField.Text rs.Update

Needs to look something like this:

// UPDATE THE DATABASE IF BOTH TEXT FIELDS HAVE DATA If TitleField.text <> "" And CodeField.text <> "" Then db.SQLExecute("INSERT INTO Snippets (Title, Code) VALUES (TitleField.text, CodeField.text)") db.SQLExecute("COMMIT") end if

Richard, using a prepared statement is the ideal way of fixing your problem, but at a minimum change your select line to

 sql="select  SRef, Title, Code from Snippets where Title='"+ReplaceAll(selected,"'", "''")+"'"

so that if the user includes a ’ in their text it will be fixed for SQL. You should also do this when inserting text via SQL injection i.e. using the sqlexecute insert statement above which should look like

db.SQLExecute("INSERT INTO Snippets (Title, Code) VALUES ('" + ReplaceAll(TitleField.text, "'", "''") + "','" + ReplaceAll( CodeField.text, "'", "''") + "')")

HTH

Wayne

Thanks Wayne,
Would your 2 examples allow single AND double quotes to be saved correctly - or only single quotes?

Hi Richard,
Only single quotes - there shouldn’t be a problem with double quotes except when constructing the sql string itself.

This

Dim ps As SQLitePreparedStatement = db.Prepare("INSERT INTO snippets (Title, Code) VALUES (?, ?);") ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT) ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT) ps.Bind(0, TitleField.text) ps.Bind(1, CodeField.text) ps.SQLExecute If db.Error Then MsgBox db.ErrorMessage End If

Is how you’d do the insert using a prepared statement. It’s a bit more typing although autocomplete takes care of most of it.

If you INSERT/SELECT/UPDATE any data which goes through the hands of a user, then I’d suggest to use the following method (put it in a Modul) :

[code]Function SafeSQL(Extends TempString As String) As String
TempString = ReplaceAll(TempString, “’”, “’’”)

Return TempString

// example:
// rs=imDB.SQLSelect(“SELECT * FROM users WHERE Lastname=’” + ln.SafeSQL + “’ AND Firstname=’” + fn.SafeSQL +"’")
End Function
[/code]

… and a longer version of the same method would strip out any SQL keywords, as an attempt to lower the risk of sql injections (If such threat is real, then you may want to study and use SQL Prepared Statements instead.)
http://documentation.xojo.com/index.php/Database.Prepare

[code]Function SafeSQLSearch(Extends TempString As String) As String
TempString = ReplaceAll(TempString, “’”, “’’”)

// Beware of angry employees …
TempString = ReplaceAll(TempString, "DROP ", “”)
TempString = ReplaceAll(TempString, "DELETE ", “”)
TempString = ReplaceAll(TempString, "CREATE ", “”)
TempString = ReplaceAll(TempString, "SELECT ", “”)
TempString = ReplaceAll(TempString, "UPDATE ", “”)
TempString = ReplaceAll(TempString, "INSERT ", “”)

TempString = ReplaceAll(TempString, “=”, " ")
TempString = ReplaceAll(TempString, “<”, " ")
TempString = ReplaceAll(TempString, “>”, " ")
TempString = ReplaceAll(TempString, “|”, " ")

TempString = ReplaceAll(TempString, " AND ", “”)
TempString = ReplaceAll(TempString, " OR ", “”)
TempString = ReplaceAll(TempString, " NOT ", “”)
TempString = ReplaceAll(TempString, " IS TRUE ", “”)
TempString = ReplaceAll(TempString, " IS FALSE ", “”)
TempString = ReplaceAll(TempString, " EQUALS ", “”)
TempString = ReplaceAll(TempString, " SIMILAR ", “”)
TempString = ReplaceAll(TempString, " LIKE ", “”)
TempString = ReplaceAll(TempString, " EXISTS ", “”)

TempString = ReplaceAll(TempString, "EXECUTE ", “’’”)

Return TempString

// example:
// rs=imDB.SQLSelect(“SELECT * FROM users WHERE Lastname=’” + ln.SafeSQLSearch + “’”)
End Function
[/code]

That’s a bit too much. Your stripping away totally legal strings here,

For most cases I agree. Of course one always has to study the specific case.

Wow - so many answers, I am now even more lost :slight_smile:

First of all - THANK YOU ALL !

Wayne, what are the 2 question marks in your prepared statement below?
Dim ps As SQLitePreparedStatement = db.Prepare(“INSERT INTO snippets (Title, Code) VALUES (?, ?);”)

I can see no reference to it in the language reference - apart from the fact they are markers?
Do they get replaced with the result from the 2 lines of code below:

ps.Bind(0, TitleField.text) ps.Bind(1, CodeField.text)

If so, am I correct in thinking my code below should now allow for apostrophes to be saved to the database:

[code] // UPDATE THE DATABASE IF BOTH TEXT FIELDS HAVE DATA
If TitleField.text <> “” And CodeField.text <> “” Then
Dim ps As SQLitePreparedStatement = db.Prepare(“INSERT INTO snippets (Title, Code) VALUES (?, ?);”)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_BLOB)
ps.Bind(0, TitleField.text)
ps.Bind(1, CodeField.text)
ps.SQLExecute

// CHECK FOR DATABASE ERROR
If db.Error Then
MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
Return

Else
  
  // COMMIT THE CHANGES
  db.commit
  
  // UPDATE THE LISTBOX
  MainWindow.Timer2.mode = MainWindow.Timer2.ModeSingle
  
  // CLOSE THE WINDOW
  Self.Close
  
end if

End If

[/code]

Hi Richard

Yes you are correct each ? becomes a bind point for the statement.