UserGuide:SQL Injection

SQL Injection is a hacking technique where access to your database is compromised by modifying SQL statements that are sent to the database. 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 just text. This is normally not a problem, but it can be when you start to concatenate text to build an SQL statement.

The Problem

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 Task WHERE UserName = 'Paul' AND Title = '" + userValue " + "';"

If the user types nonsense data such as "Mow" as the value then the SQL looks like this:

SELECT * FROM Task WHERE UserName = 'Paul' AND Title = 'Mow';

This would not return any rows.

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 Task 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 is a comment so is ignored. 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.

Use Prepared Statements

So how do you avoid this? Instead of concatenating, you use the database's built-in ability to create prepared statements by binding values to parameters.

To start you first declare the SQL to have parameters:

Var sql As String
sql = "SELECT * FROM Tasks WHERE UserName = 'Paul' AND TITLE = ?;"

Then you create send the SQL to the database along with the values to bind to the parameter:

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 more like this:

SELECT * FROM Tasks WHERE UserName = 'Paul' AND TITLE = 'Mow'' OR 1;'"

This SQL statement will return no rows, which is what you want.

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 performance improvements if the database command is used repeatedly throughout the app.

Note that if you use DatabaseRow or RowSet EditRow/SaveRow, you are also protected from SQL injection.

UserGuide

SQL Injection

From Xojo Documentation

Contents

The Problem

Use Prepared Statements

See Also