Database.ExecuteSQL
From Xojo Documentation
You are currently browsing the old Xojo documentation site. Please visit the new Xojo documentation site! |
Supported for all project types and targets.
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
A DatabaseException will be raised if the SQL passed is invalid or if the SQL could not be executed due to an error.
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:
Var sql As String
sql = "UPDATE Customer SET City='" + CityField.Text + "' WHERE PostalCode='" + PostalCodeField.Text + "'"
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:
Var sql As String
sql = "UPDATE Customer SET City=? WHERE PostalCode=?"
Try
db.ExecuteSQL(sql, CityField.Text, PostalCode.Text)
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
End Try
The parameter values can also be passed in as a variant array:
sql = "UPDATE Customer SET City=? WHERE PostalCode=?"
Var values(1) As Variant
values(0) = CityField.Text
values(1) = PostalCode.Text
Try
db.ExecuteSQL(sql, values)
Catch error As DatabaseException
MessageBox("DB Error: " + error.Message)
End Try
This code creates the Team table: