SQL Prepared Statement comes to REALbasic

In REAL Studio 2010 Release 4, the REALbasic language gains new interfaces and classes to support SQL Prepared Statement. This feature is called Database Parameter Binding.

A prepared statement is a SQL thing, where the query is pre-processed on the database server before it is actually used. The parsing of the SQL statement's text and verifying the table/column names is done ahead of time. The query planner in the database server figures out how to solve the query without actually executing one. Then you can execute the same query many times, passing arguments. Each successive use of that query saves you the overhead of parsing, processing, and planning by the database server. Not much difference for a low use database, but this is an easy way to greatly improve performance on a busy server. 

Another advantage is simplification of your programming code. It is easier to read calls to a prepared statement where you are passing the arguments for various column values rather than reading the entire text of a full SQL statement.

Besides performance on the server, and convenience in your code, there is another big advantage of the prepared statement: Security. This approach fights against “SQL Injection” attacks, where the bad guys put partial SQL statements in the fields of your form. With a regular SQL call, you’ll be unwittingly altering your SQL statement on-the-fly at runtime when those field values are interpreted as SQL commands. With a prepared statement, the SQL statement is already built and the query planner has already done its work before the form's data is incorporated. So any partial SQL in the form's fields will be treated as plain text rather than interpreted as SQL.

For more info on SQL Injection:

• Give me parameterized SQL, or give me death
• SQL Injection Attacks by Example

Here's an example of the prepared statement in action in REAL Studio.

dim db as new PostgreSQLDatabase
// Set db.xxx properties. IP address, db name, user name, user password, etc.

dim stmt as PostgreSQLPreparedStatement = PostgreSQLPreparedStatement(db.Prepare("SELECT * FROM cats WHERE name = $1"))

// 1 query, used 3 times.
// "Sebastian" is fed into the "$1" in line above.
Dim rsGoodKitty As RecordSet = stmt.SQLSelect("Sebastian")
Dim rsBadKitty As RecordSet = stmt.SQLSelect("George")
Dim rsCartoonKitty As RecordSet = stmt.SQLSelect("Sylvester")



The "$1" syntax above is specific to Postgres. Other databases may use a different symbol, typically "?" question mark. Furthermore, other database may require additional syntax to specify the data types of passed arguments.

The prepared statement is new to REAL Studio, but not to SQL or other programming languages such as Java. So don't be afraid to start using it if you upgrade to this new release. In at least Postgres, a prepared statement can be used on the SELECT, INSERT, UPDATE, DELETE, or VALUES commands.

Preliminary doc is here. The online wiki-based docs are not yet updated.

And some Postgres docs about prepared statements and its PREPARE command.