input sanitization.

I don’t think I fully understand input sanitizing.

I have a method which doesn’t seem to work well. As you can imagine I’ve been testing and using my name as it has an apostrophe in it.
I’m noticing that my name goes into the database but when extracted comes out as O’'Brien.

Function PrepSQL(extends s as string) As String dim quote as string = "'" s = ReplaceAll(s, quote, quote+quote) return s End Function

Don’t sanitize, use prepared statements instead. Apostrophes aren’t the only thing that can cause you trouble. Prepared statements allow the database engine to do the sanitation for you.

People sometimes don’t get prepared SQL.
In short, Prepared SQL is a “compiled” and more secure version of your “SQL script”. It can be faster if reused few times.

A prepared statement is a kind of “dynamically compiled function” (not a machine code as Xojo, but a fast internal code based in a execution plan), and the number of the parameters you “defined” when you prepared your SQL script. As more “intelligent” is your DB server, more it optimizes the speed with the reuse. So, after prepared, executing it is similar to calling something like:

DoAnInsertInPersonsTable(FirstName, LastName) // Kind of, see below in “Xojo example”

instead of

db.SQLExecute(“INSERT INTO persons (firstname, lastname) VALUES (”+FirstName+", “+LastName+”)") // Here you inject “possibilities”

//------------------------- Xojo example (Postgre): --------------------

Dim InsertPerson As PreparedSQLStatement = db.Prepare(“INSERT INTO persons (firstname, lastname) VALUES ( $1, $2)”)
InsertPerson.SQLExecute( “Rick”, “A”)
insertPerson.SQLExecute(“Beer”, “O’Brian”)
insertPerson.SQLExecute( “Any”, “Thing including injections '); DELETE FROM persons; --”)
insertPerson.SQLExecute( sParm1, sParm2 )