Most database bugs in Xojo apps aren't subtle. They're the same three mistakes, repeated across thousands of codebases, by developers who either didn't know better or figured they'd fix it later.
Later never comes. The string-concatenated query ships. The missing error handler stays missing. The database layer stays tangled with the UI. And six months later, someone is debugging a production crash at the worst possible time.
This post is about doing it right from the start. Prepared statements, proper error handling, and a data layer architecture that holds up over time.
The Problem With String-Concatenated Queries
Here's a query you'll see in Xojo codebases written by developers who learned from older tutorials:
// New API (Xojo 2019r2+)
// WRONG — never do this
Dim username As String = UsernameField.Text
Dim query As String = "SELECT * FROM users WHERE username = '" + username + "'"
Dim rs As RowSet = db.SelectSQL(query)This works. Right up until it doesn't.
If username contains a single quote, say a user named O'Brien, the query breaks. If it contains ' OR '1'='1, your query returns every row in the table. If it contains '; DROP TABLE users; --, you know where this is going.
SQL injection isn't a theoretical risk. It's a well-documented, actively exploited attack vector. And string concatenation is the door that lets it in.
The fix isn't complicated. It's a PreparedStatement.
PreparedStatements: The Right Way to Query
A PreparedStatement separates the query structure from the data. The database engine receives the query as a template, then receives the values separately. User input is never interpreted as SQL. It's always treated as data.
Here's the same query, done correctly:
// New API (Xojo 2019r2+)
// Correct — use a PreparedStatement for any query with user-supplied values
Dim ps As SQLitePreparedStatement = _
db.Prepare("SELECT * FROM users WHERE username = ?")
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, UsernameField.Text)
Dim rs As RowSet = ps.SelectSQL()
The ? placeholder is where the value goes. The Bind call supplies the value. The database engine handles the rest: escaping, quoting, type coercion. You don't touch any of it.
For INSERT and UPDATE operations, the same pattern applies:
// New API (Xojo 2019r2+)
// INSERT with PreparedStatement
Dim ps As SQLitePreparedStatement = _
db.Prepare("INSERT INTO users (username, email, created_at) VALUES (?, ?, ?)")
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, username)
ps.Bind(1, email)
ps.Bind(2, DateTime.Now.SQLDateTime)
ps.ExecuteSQL()
Note the BindType call before each Bind. Xojo's SQLite prepared statement implementation requires you to declare the type before binding the value. Skip it, and the binding silently fails or behaves unexpectedly. Always pair them.
Error Handling: The Happy Path Is Not Enough
I've reviewed a lot of Xojo database code. The most common failure isn't bad queries. It's missing error handling. The developer wrote for the happy path, shipped it, and got a crash report when something unexpected happened.
Database operations fail. Connections drop. Disk space runs out. Constraints get violated. Your code needs to handle these cases, every time.
Xojo's database classes use exceptions for error reporting. Wrap every database operation in a Try...Catch block:
// New API (Xojo 2019r2+)
// Correct error handling for a database query
Try
Dim ps As SQLitePreparedStatement = _
db.Prepare("SELECT id, username, email FROM users WHERE id = ?")
ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(0, userId)
Dim rs As RowSet = ps.SelectSQL()
If rs <> Nil And Not rs.AfterLastRow Then
// Process results
Do Until rs.AfterLastRow
// Access columns by name — more resilient than by index
Dim name As String = rs.Column("username").StringValue
Dim email As String = rs.Column("email").StringValue
rs.MoveToNextRow
Loop
End If
Catch e As DatabaseException
// Log the error — don't silently swallow it
System.Log(System.LogLevelError, "Query failed: " + e.Message)
// Surface it appropriately to the caller or the user
Raise e
End TryA few things worth noting in that pattern:
Access columns by name, not by index. rs.Column("username") is more resilient than rs.Column(0). If the query changes and the column order shifts, named access still works. Index access silently returns the wrong value.
Don't swallow exceptions silently. A Catch block that does nothing is worse than no error handling at all. It hides failures and makes them impossible to diagnose. Log it. Re-raise it. Surface it to the user. Do something.
Check for Nil before using a RowSet. A failed query can return Nil. An empty result set is not Nil. It's a valid RowSet where AfterLastRow is immediately true. Know the difference.
Transactions: Protecting Related Operations
When you have a group of database operations that must all succeed or all fail together, use a transaction. Without one, a partial failure leaves your data in an inconsistent state.
The pattern is straightforward:
// New API (Xojo 2019r2+)
// Transaction wrapping multiple related operations
Try
db.BeginTransaction
Dim ps1 As SQLitePreparedStatement = _
db.Prepare("INSERT INTO orders (customer_id, total) VALUES (?, ?)")
ps1.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
ps1.BindType(1, SQLitePreparedStatement.SQLITE_DOUBLE)
ps1.Bind(0, customerId)
ps1.Bind(1, orderTotal)
ps1.ExecuteSQL()
Dim orderId As Integer = db.LastRowID
Dim ps2 As SQLitePreparedStatement = _
db.Prepare("INSERT INTO order_items (order_id, product_id, quantity) VALUES (?, ?, ?)")
ps2.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
ps2.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
ps2.BindType(2, SQLitePreparedStatement.SQLITE_INTEGER)
For Each item As OrderItem In items
ps2.Bind(0, orderId)
ps2.Bind(1, item.ProductId)
ps2.Bind(2, item.Quantity)
ps2.ExecuteSQL()
Next
db.CommitTransaction
Catch e As DatabaseException
// Roll back everything if any step fails
db.RollbackTransaction
System.Log(System.LogLevelError, "Order save failed: " + e.Message)
Raise e
End Try
BeginTransaction, do the work, CommitTransaction. If anything throws, RollbackTransaction. The data is always in a valid state.
Transactions also have a performance benefit. SQLite, in particular, is dramatically faster when writes are batched inside a transaction rather than executed individually. If you're inserting or updating large numbers of rows in a loop, wrapping the loop in a transaction isn't just safer. It's measurably faster.
Separating the Database Layer from Your UI
This is where architecture matters. A lot of Xojo apps put database calls directly in button event handlers, window open events, and list population methods. It works. It's also a maintenance problem that compounds over time.
When your SQL is scattered through UI event handlers, every change to a query requires you to hunt through UI code. Testing logic requires opening windows. Reusing a query means copying it, and then keeping two copies in sync.
The better pattern is a dedicated data layer: a module or set of classes whose only job is database access. Your UI calls methods on the data layer. It never writes SQL.
A simple example:
// New API (Xojo 2019r2+)
// A dedicated data access method — lives in a Module, not a Window
Function GetUserByID(db As SQLiteDatabase, userId As Integer) As RowSet
Dim ps As SQLitePreparedStatement = _
db.Prepare("SELECT id, username, email FROM users WHERE id = ?")
ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(0, userId)
Return ps.SelectSQL()
End FunctionYour UI code becomes:
// New API (Xojo 2019r2+)
// Clean UI code — no SQL, no database details
Dim rs As RowSet = GetUserByID(App.Database, selectedUserId)
If rs <> Nil And Not rs.AfterLastRow Then
UsernameLabel.Text = rs.Column("username").StringValue
End IfThe UI doesn't know what database engine you're using. It doesn't know what the query looks like. It calls a method, gets data back, and displays it. When the query changes, you change it in one place.
The Crawl, Walk, Run Path
Crawl: Use PreparedStatement for every query that takes user input. No exceptions. Build that habit before anything else.
Walk: Add Try...Catch to every database operation. Log errors. Verify your RowSet checks are correct. Stop writing for only the happy path.
Run: Refactor your database calls into a dedicated data layer. Keep SQL out of UI event handlers. Design methods that do one thing, return clean results, and can be reused across your application.
The Bottom Line
Database code that works in your test environment and breaks in production almost always has one of three problems: string concatenation instead of prepared statements, missing error handling, or data access logic tangled with UI code.
None of these are hard to fix. They're just easy to skip when you're moving fast.
Use PreparedStatement. Handle exceptions. Keep your SQL out of your windows.
The data your users trust you with deserves that much.