Maybe you’ve heard the term SQL injection and how it can allow unintended database access. Here’s how a SQL Injection works and what you can do to avoid it.
Generally, you work with databases by sending SQL commands. In particular the SELECT command is used to get data from a database. These SELECT statements are simply built out of text. This is normally not a problem, but it can be when you start to concatenate text to build an SQL statement.
In particular, the risk occurs when you build an SQL statement with user-supplied input. For example, say you prompt the user for some information to filter (it doesn’t matter what) and then create an SQL statement by concatenating what they typed. You might have code that looks like this:
Dim sql As String sql = "SELECT * FROM Tasks WHERE UserName = 'Paul' AND Title = '" + userValue " + "'"
If the user types “Mow” as the value then the SQL looks like this:
SELECT * FROM Tasks WHERE UserName = 'Paul' AND Title = 'Mow'
But what if the user types something a bit more nefarious, such as “Mow’ OR 1;”? This now results in SQL that looks like this:
SELECT * FROM Tasks WHERE UserName = 'Paul' AND Title = 'Mow' OR 1;'
Now there are two SQL statements. The first one returns all the data in the table (because of the “OR 1”) and the second statement (just a ‘) is likely just ignored. So now your app is displaying lots of data that the user was not meant to see (perhaps data from other users in this case).
This is called SQL Injection because the user was able to inject their own SQL into your query, all because you were using concatenation.
So how do you avoid this? Instead of concatenating, you use the databases built-in ability to create prepared statements by binding values to parameters. For Xojo, you use the appropriate PreparedStatement class for your database. In the case of SQLite, you would use SQLitePreparedStatement.
To start you first declare the SQL to have parameters:
Dim sql As String sql = "SELECT * FROM Tasks WHERE UserName = 'Paul' AND TITLE = ?"
Then you create a prepared statement, bind the value and send it to the database:
Dim ps As SQLitePreparedStatement ps = MyDB.Prepare(sql) ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT) ps.Bind(0, userValue) Dim rs As RecordSet rs = ps.SQLSelect
Update (October 10, 2019):
If you’re using API 2.0 in 2019r2 or later, the above code is even simper:
Var results As RowSet = MyDB.SelectSQL(sql, userValue)
Because the value is treated as a parameter, if evildoers again try to inject their own SQL, the resulting SQL sent to the database is actually equivalent to this:
SELECT * FROM Tasks WHERE UserName = 'Paul' AND TITLE = 'Mow'' OR 1;'"
This is a valid SQL statement that will simply return no rows.
Any apps that embed user input into SQL statements should always use prepared SQL statements. Do not think you can just write your own code to sanitize user input as this still poses a risk.
And there are also reasons to use prepared statements even when SQL injection is not a concern: some databases cache the parsing of the database command, which can result in some performance improvements if the database command is used repeatedly in the app.
Reference: SQL Injection in the Xojo User Guide
