Statement Objects

Statement objects support fine-grained control over statements sent to a database. We can create a statement object by sending the createStatement: message to a connection object with a SQL statement string, or the createStatement and then configuring the statement with the statementString: message.

aStatement := connection createStatement: 'CREATE TABLE CATS (
	name varchar(50),
	owner varchar(50))'.

anotherStatement := connection createStatement.
anotherStatement statementString: 'SELECT * FROM CATS'.

Statement objects can be executed sending them the execute message. Executing a statement returns the same kind of results as using the execute: message from a connection object. In fact, the connection object uses a statement object underneath.

result := aStatement execute.
result do: [ ... ].

Statement objects can be reused. We can send them multiple times the execute message and obtain our results multiple times. This is particularly useful to avoid statements with complex creations or already optimized by the database, as we will see later on.

result := aStatement execute.
result do: [ ... ].

"we can execute it again and obtain the same results"
aStatement execute do: [ ... ].

Prepared Statements

Some drivers support prepared statements. A prepared statement is a statement that is sent to the database to pre-process it. The database server can pre-parse it, cache results or even pre-calculate how it will execute the query. A prepared statement can be created by sending the prepare: message to a connection with a SQL statement string, or by sending the prepare message to an already existing statement.

preparedStatement := connection prepare: 'SELECT * FROM CATS'.

anotherPreparedStatement := aStatement createStatement: 'SELECT * FROM CATS'.
anotherPreparedStatement prepare.

Although prepared statements benefits, they are not equally supported by every database nor driver. Different databases support the preparation of different kind of statements, and not all database drivers support prepared statements. When a database driver supports prepared statements, the statement is sent to the database for its preparation immediately after the prepare message. If the sent statement cannot be prepared by the database, an exception will be thrown notifying it. If a database driver does not support prepared statements, the preparation will also fail with an exception. To know if a driver supports prepared statements, we can send the message supportsPreparedStatements to a database connection.

aConnection supportsPreparedStatements
	ifTrue: [ ... ]
	ifFalse: [ ... ]

TODO: provide a table with the drivers that support it. Also table of what kind of statements can be prepared by database

ask the database which kind of statements it supports for preparation

1. Sanitizing SQL

Composing strings to create a SQL statement can be cumbersome but also an unsafe way of creating a query. Attackers may benefit from wrongly sanitized SQL to perform SQL injection: the injection of SQL attacks inside a query. For example, considering the following SELECT query

SELECT * FROM TABLE WHERE name=<some param>

If we naively concatenate it with the string we receive as an argument from a user, the user could be able to inject a DROP TABLE script like this:

SELECT * FROM CATS WHERE name='';DROP TABLE CATS;

Prepared statements provide means to safely insert parameters inside a SQL statement. These parameters or placeholders are denoted inside a query by different literals, depending on the database server. It is the work of the database and its driver to safely replace the placeholder by its correct value and handle the escaping of such code to avoid injection.

• Question mark placeholders (?) are positional placeholders. Their index is their order of appearance.
• Numbered placeholders are identified by a question mark or a dollar sign followed by an integer number (?1, ?2, $1, $2). Their index is the number associated to them.
• Named placeholders are identified by a dollar sign, an at sign or a collon followed by an name number ($name, @name, :name). Their index is the number associated to them. Usually, two named placeholders with the same name need to be set only once as they denote the same value.

SELECT * FROM TABLE WHERE name='?'
SELECT * FROM TABLE WHERE name='$1'
SELECT * FROM TABLE WHERE name=':name'

The DBXTalk driver, by pursuing a common interface between different database drivers, only proposes question mark placeholders (?) in a SQL statement.

A statement placeholders can be filled with arguments using the at:bind: message. The at:bind: message accepts as argument any Pharo object and will transform it accordingly to its database representation. If no bind is specified, it is responsability of the driver to decide what to do. For example, SQLite3 will interpret missing bindings as NULL.

TODO: provide a table with these defaults.

statement := connection prepare: 'SELECT * FROM CATS WHERE name='?''.
statement at: 1 bind: 'lutz'.
result := statement execute

If we try to do SQL injection using placeholder bindings, it will escape the argument creating a safe version of the SQL statement. In this case the injected code will be embedded inside a string and the query will execute safely.

statement := connection prepare: 'SELECT * FROM CATS WHERE name='?''.
statement at: 1 bind: ''''';DROP TABLE TABLE;'.
result := statement execute.

SELECT * FROM CATS WHERE name=''';DROP TABLE TABLE;'

A SQL statement can also contain many placeholders. In that case, the supplied bindings are set in order:

statement := connection prepare: 'SELECT * FROM CATS WHERE name='?' OR owner='?''.
statement at: 1 bind: 'lutz'.
statement at: 2 bind: 'guille'.
result := statement execute.

SELECT * FROM CATS WHERE name='lutz' OR owner='guille'