Difference between revisions of "Database.ExecuteSQL"

From Xojo Documentation

(Notes)
Line 23: Line 23:
 
If the SQL passed is invalid, a [[DatabaseException]] will occur.
 
If the SQL passed is invalid, a [[DatabaseException]] will occur.
  
You can pass an entire valid SQL statement to ExecuteSQL with all values included. However, doing so can leave your database open to an [[UserGuide:SQL Injection|SQL injection attack]].
+
=== Parameters ===
 
+
To avoid [[UserGuide:SQL Injection|SQL injection attack]]s, use parameters in your SQL statement and then pass the values in as an array or parameter array. See the examples below.
To avoid this, use value identifiers in your SQL statement and then pass the values in as an array or parameter array. See the examples below.
+
{| class="genericTable"
 +
! width="30%" | Database
 +
! width="20%" | Parameter Format
 +
|-
 +
|[[MSSQLServerDatabase]]
 +
|?
 +
|-
 +
|[[MySQLCommunityServer]]
 +
|?
 +
|-
 +
|[[ODBCDatabase]]
 +
|?
 +
|-
 +
|[[OracleDatabase]]
 +
|:columnname
 +
|-
 +
|[[PostgreSQLDatabase]]
 +
|$1, $2, etc.
 +
|-
 +
|[[SQLiteDatabase]]
 +
|?, ?NNN, :VVV, @VVV, $VVV ([https://www.sqlite.org/lang_expr.html#varparam see docs])
 +
|-
 +
|-
 +
|}
  
 
== Sample Code ==
 
== Sample Code ==
In this example, the database is being updated without the use of value identifiers and thus leaves the database vulnerable to a [https://en.wikipedia.org/wiki/SQL_injection SQL injection attack]:
+
In this example, the database is being updated without the use of parameters and thus leaves the database vulnerable to a [[UserGuide:SQL Injection|SQL injection attack]]:
 
<rbcode>
 
<rbcode>
 
// Updates a table in a SQLite database (db)
 
// Updates a table in a SQLite database (db)
Line 40: Line 63:
 
</rbcode>
 
</rbcode>
  
Here's the same example but using value identifiers which protects you against a [https://en.wikipedia.org/wiki/SQL_injection SQL injection attack]:
+
Here's the same example but using parameters which protects you against a [[UserGuide:SQL Injection|SQL injection attack]]:
 
<rbcode>
 
<rbcode>
 
// Updates a table in a SQLite database (db)
 
// Updates a table in a SQLite database (db)
Line 52: Line 75:
 
</rbcode>
 
</rbcode>
  
The values can also be passed in as a variant array:
+
The parameter values can also be passed in as a variant array:
 
<rbcode>
 
<rbcode>
 
Var sql As String
 
Var sql As String

Revision as of 21:45, 22 November 2019

Method

Database.ExecuteSQL(SQLStatement as String [,ParamArray values() as Variant])

Supported for all project types and targets.


Method

Database.ExecuteSQL(SQLStatement as String [,values() as Variant])

Supported for all project types and targets.


Used to execute an SQL command. Use this for commands that do not return any data, such as CREATE TABLE or INSERT. SQLStatement contains the SQL statement.

Notes

If the SQL passed is invalid, a DatabaseException will occur.

Parameters

To avoid SQL injection attacks, use parameters in your SQL statement and then pass the values in as an array or parameter array. See the examples below.

Database Parameter Format
MSSQLServerDatabase ?
MySQLCommunityServer ?
ODBCDatabase ?
OracleDatabase :columnname
PostgreSQLDatabase $1, $2, etc.
SQLiteDatabase ?, ?NNN, :VVV, @VVV, $VVV (see docs)

Sample Code

In this example, the database is being updated without the use of parameters and thus leaves the database vulnerable to a SQL injection attack:

// Updates a table in a SQLite database (db)
Var sql As String
sql = "UPDATE Customer SET City='" + CityField.Value + "' WHERE PostalCode='" + PostalCodeField.Value + "'"
Try
db.ExecuteSQL(sql)
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
End Try

Here's the same example but using parameters which protects you against a SQL injection attack:

// Updates a table in a SQLite database (db)
Var sql As String
sql = "UPDATE Customer SET City=? WHERE PostalCode=?"
Try
db.ExecuteSQL(sql, CityField.Value, PostalCode.Value)
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
End Try

The parameter values can also be passed in as a variant array:

Var sql As String
sql = "UPDATE Customer SET City=? WHERE PostalCode=?"
Var values(1) As Variant
values(0) = CityField.Value
values(1) = PostalCode.Value
Try
db.ExecuteSQL(sql, values)
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
End Try

This code creates the Team table:

// db is a SQLite database
Var sql As String
sql = "CREATE TABLE Team (ID INTEGER NOT NULL, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID));"
Try
db.ExecuteSQL(sql)
MessageBox("Team table created successfully.")
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
End Try