Replacing Characters in a textfield or textArea

Hi Folks.

I’m just full of strange questions…

I have two entries into my sqlite Database.
One is a textfield, the other a text Area.

I need to change ’ (apostrophes) as they stop the entry from writing.

I use this code to replace the characters. The textfield works, the textArea doesn’t.

replacednameTextField = nameTextField.text.ReplaceAll("’","")
replacednotesTextArea = notesTextArea.text.ReplaceAll ("’","
")

Can anyone tell me why?

Regards

Neither should work as you are attempting to assign a string to a control. Did you write it like this in both instances?

replacednameTextField.Text = nameTextField.text.ReplaceAll("’","")
replacednotesTextArea.Text = notesTextArea.text.ReplaceAll ("’","")

But aside from that, you should not be doing it this way. You should use a PreparedStatement or a DatabaseRow to insert your data. If you are using the new ExecuteSQL, this is made easier.

3 Likes

Hi Kem.

Yes I did. All these need to do is replace the single quotes as when there are single quotes, the record doesn’t enter into the database.

Regards

A single quote (apostrophe) is a reserved character in SQL. So if you have a string of text that contains a single quote, in SQL you need to escape the single quote by using another single quote, like so:

UPDATE someTable
SET Name = 'Greg O''Lone'
WHERE ID = 1234

Which might mean, using a search-and-replace in your Xojo code that substitutes ' with '' (2x single quotes, not a double-quote).

I hope that helps.

1 Like

I’ll give it a try and let you know.
Thanks for the tip scott

Regards

1 Like

Hi Scott.

That seemed to work… yaaay.
I’ll do a little bit more testing… just to make sure!

Once again, thank you.

Hi Scott.

It is an ALMOST working.
On my mac, it works fine.

On a windows 10 machine, the record with an ’ isn’t written to the database.

Regards

Hmm,

The single quote issue is specifically about character Encodings.ASCII.Chr(39).

Maybe on Windows you’ve got a curved or curled single quote in your Xojo code? Something that is not Chr(39)?

Use a prepared statement.

6 Likes

You should not be using SQL Injection for data that is entered by users. If you use prepared statements you won’t have to worry about the single quotes either.

Var SQL As String = "UPDATE someTable SET Name = ? WHERE ID = 1234;"

db.Execute(SQL, "Greg O'Lone")
3 Likes

thank you for all the responses.
I’ll look up prepared statements, as I have never used them before, and advise.

I know these questions are simple for you, but as I said, I am self taught. Not an excuse, just a statement… which will soon become a PREPARED statement… :grin:

Regards

2 Likes

Once you learn about prepared statements, you’ll wonder how you got along without them. More than that, you’ll wonder why you got along without them.

The new ExecuteSQL and SelectSQL statements make them even easier to use. Let’s say you wanted to insert string data into your SQLite database. You’d do it like this:

var sql as string = _
  "INSERT INTO my_table (col1, col2) VALUES (?, ?)"

db.ExecuteSQL( sql, myString1, myString2 )

The engine swaps out the question marks with your values.

Later, you want to query the table:

sql = "SELECT * FROM my_table WHERE col1 = ? AND col2 = ?"
var rs as RowSet = db.SelectSQL( sql, myString1, myString2 )

You do not need to worry about the contents of the variables, so apostrophes, or anything else, can remain as entered, and there is no chance of error or SQL injection caused by “bad” content.

3 Likes

Hi there, Kem.
I took all the good folks advice and started playing with prepared statements before modifying my main program.

I can read with no problem from my database with a select, but I can’t insert.
Here is the code where I have 2 textfields and 1 area that I can enter data in… for checking the apostrophe. The commented out items are for future testing. I’m just using textfield1 to work out the bugs…

// new test of preparedSQL statement for insert

dim db as SQLiteDatabase
dim dbfile as FolderItem
dim rows as integer
dim i as integer
dim workRS as Recordset
Var ps As SQLitePreparedStatement

dbfile = getopenFolderItem("")

if dbfile <> NIL then
  db = new SQLiteDatabase
  db.DatabaseFile = dbfile
  
  if db.Connect then
    
    MessageBox "Connected"
    
    ps = SQLitePreparedStatement(db.Prepare("INSERT INTO Activity (groupType, Name, startTime, endTime, Notes, seconds, minutes, hours, tech3, tech2, tech1) VALUES (?,?,?,?,?,?,?,?,?,?,?"))
    
    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.BindType(4, SQLitePreparedStatement.SQLITE_TEXT)
    'ps.BindType(5, SQLitePreparedStatement.SQLITE_TEXT)
    'ps.BindType(6, SQLitePreparedStatement.SQLITE_TEXT)
    'ps.BindType(7, SQLitePreparedStatement.SQLITE_TEXT)
    'ps.BindType(8, SQLitePreparedStatement.SQLITE_TEXT)
    'ps.BindType(9, SQLitePreparedStatement.SQLITE_TEXT)
    'ps.BindType(10, SQLitePreparedStatement.SQLITE_TEXT)
    
    ps.Bind(0, TextField1.Text) // for group, with an apostrophe
    'ps.Bind(1, TextField2.Text) // for name with apostrophe
    'ps.Bind(2, "2021-04-13 21:20:20") //start time
    'ps.Bind(3, "2021-04-13 22:20:20")//end time
    'ps.Bind(4, TextArea1.Text) //notes with apostrophe
    'ps.Bind(5, "600") //seconds
    'ps.Bind(6, "10") //minutes
    'ps.Bind(7, "0.166666") //hours
    'ps.Bind(8, "Fred Flintstone") //techname
    'ps.Bind(9, "000-000-0001") //cell phone
    'ps.Bind(10, "000-000-0002")// office phone
    
    ps.SQLExecute ()
    
  end if
end if

Can you see what boneheaded mistake I’m making?

Regards

I’m beginning to think I should be charging a fee to use my name in examples…

4 Likes

Or change your last name :stuck_out_tongue:

I just spent $145 on a new passport, so I’m not doing that…

On the “ps =“ line the last quote needs to go outside the second to last parentheses, not before it.

@Anthony_Mott saw one error. But also, you can’t create placeholder tokens without filling them. And ExecuteSQL makes the whole thing easier:

var sql as string = _
    "INSERT INTO Activity (groupType, Name, startTime, endTime, Notes, seconds, minutes, hours, tech3, tech2, tech1) VALUES (?,?,?,?,?,?,?,?,?,?,?)"

db.ExecuteSQL( sql, _
    TextField1.Text, _
    TextField2.Text, _
    "2021-04-13 21:20:20", _
    "2021-04-13 22:20:20", _
    TextArea1.Text, _
    "600", _
    "10", _
    "0.166666", _
    "Fred Flintstone", _
    "000-000-0001", _
    "000-000-0002" )
1 Like

Using your name?
I made all this stuff up Greg.

And $145 for a passport? I paid roughly the same for mine. I’m in Canada.
What country you hang out it?

Regards

1 Like

Hi All.
Thanks to the advice here, I found my problem.
Anthony and Kem:

This line works:
ps = (db.Prepare("INSERT INTO Activity (groupType, Name, startTime, endTime, Notes, seconds, minutes, hours, tech3, tech2, tech1) VALUES (?,?,?,?,?,?,?,?,?,?,?)"))

If we look at my ORIGINAL line
ps = SQLitePreparedStatement(db.Prepare("INSERT INTO Activity (groupType, Name, startTime, endTime, Notes, seconds, minutes, hours, tech3, tech2, tech1) VALUES (?,?,?,?,?,?,?,?,?,?,?"))

I have SQLitePreparedStatement… which I also have in my instatiation.

Now to test with apostrophes

Regards

Forum for Xojo Programming Language and IDE. Copyright © 2021 Xojo, Inc.