Prepared Statements

As of version 1.1.0 mSQLite supports prepared statements. Prepared statements, in simple terms, are a way to prepare a query before actually executing it, you can think of it as a compiled SQL template of a sort. There are two major benefits of using prepared statements:

• In case you want to execute the same query, only with different parameters, more than one time the query only needs to be prepared (or compiled) once. This can offer a performance boost for complex queries.
• They're safe from SQL injections if parameter binding is used. In other words, when parameters are bound with the binding API that mSQLite supports, no data needs to be quoted, SQLite will handle it for you.

It's important to remember that prepared statements aren't always necessarily faster than unprepared statements, even when you execute a large set of same query. The complexity of the query is the major factor.

To prepare a query you need to use $sqlite_prepare. It works very much like $sqlite_query, except that instead of returning a result set produced by the query, it returns a prepared statement.
Example of preparing a statement and then executing it:

Code ; Assumes that %db is already open, and table contacts exists in the database var %sql = INSERT INTO contacts (name, email) VALUES ('Joe', '[email protected]') var %stmt = $sqlite_prepare(%db, %sql) sqlite_exec %stmt sqlite_free %stmt

Of course using prepared statements like this isn't useful. Their usefulness comes from being able to bind parameters to the SQL query. Binding parameters is a way to substitute certain parts of the query, with values specified with the binding API. Here's a the previous example but with more meaningful query:

Code ; Assumes that %db is already open, and table contacts exists in the database var %sql = INSERT INTO contacts (name, email) VALUES (?, ?) var %stmt = $sqlite_prepare(%db, %sql) sqlite_bind_value %stmt 1 Joe sqlite_bind_value %stmt 2 [email protected] sqlite_exec %stmt sqlite_free %stmt

The values specified with question marks in the query are later substituted with Joe and [email protected] respectively.

This section is to show what different types of binding parameters mSQLite support. Each type of binding falls into one of the two categories:

• Automatic input binding
  Even before version 1.1.0 mSQLite supported this type in form of binary variables. In 1.1.0 you can also bind regular variables with similiar syntax, using @ to prefix the variable name. Here's an example that shows how to bind a regular variable and a binary variable:

  Code set %image_name mIRC Logo bread mIRC.png 0 $file(mIRC.png).size ℑ_bytes var %sql = INSERT INTO images (name, bytes) VALUES (@image_name, ℑ_bytes) sqlite_exec %db %sql

  Note that variables bound with automatic binding must be global variables and can only contain letters, numbers and underscores.
• Manual input binding
  Manual input binding always requires a prepared statement. There are three ways to bind parameters: sqlite_bind_param, sqlite_bind_value and binding them as extra arguments for sqlite_query, sqlite_exec and sqlite_unbuffered_query. The first way is similiar to automatic binding of variables, with the exception being that you can use more complex naming for the variables and you can specify the type of the variable if needed. Example:

  Code ; Contact info set %contact.name Joe set %contact.tel 123456789 ; Insert contact var %sql = INSERT INTO contacts (name, tel) VALUES (:name, :tel) var %stmt = $sqlite_prepare(%db, %sql) sqlite_bind_param %stmt :name contact.name sqlite_bind_param %stmt :tel contact.tel $SQLITE_TEXT sqlite_exec %stmt sqlite_free %stmt

  With manual binding of variables, you can use variable names such as %contact.name, that contain special characters like period. With automatic binding you can't do this. Another demonstrated feature that isn't possible with automatic binding is specifying the type for the bound variable. In this particular case we're telling mSQLite that the telephone number should always be considered a text. If we didn't do this, mSQLite would consider it as a number since it contains only numbers.
  
  Both types of binding, sqlite_bind_param and sqlite_bind_value, support both named parameters and numeric parameters. Numeric parameters are specified with a question mark in the query, named parameters are prefixed with a colon. Here's a couple of examples to demonstrate them:

  Code ; Numeric parameters var %sql = INSERT INTO contacts (name, tel) VALUES (?, ?) ; Named parameters var %sql = INSERT INTO contacts (name, tel) VALUES (:name, :tel) ; Numeric and named parameters var %sql = INSERT INTO contacts (name, tel) VALUES (:name, ?2)

  The third way of binding parameters to prepared statements is most likely the most intuitive one if you only need numeric parameters and don't want to re-use global variables as bound parameters nor call sqlite_bind_value a bunch of times each time you re-execute the statement. An example:

  Code ; Insert contact var %sql = INSERT INTO contacts (name, tel) VALUES (?, ?) var %stmt = $sqlite_prepare(%db, %sql) noop $sqlite_exec(%stmt, Joe Gibson, 123456789) sqlite_free %stmt

  Note that we use /noop here because the name contains a space. By using noop and calling the sqlite_exec as an identifier instead of as a command, we can make mIRC tokenize the parameters correctly.
  
  As of version 1.2.0 you can bind parameters to non-prepared statements using the same intuitive syntax as above. The only limitation is that you must call the query functions using the identifier format (i.e. $sqlite_query instead of /sqlite_query):

  Code ; Insert contact var %sql = INSERT INTO contacts (name, tel) VALUES (?, ?) noop $sqlite_exec(%db, %sql, Joe Gibson, 123456789) ; Select all contacts with name Joe Gibson var %sql = SELECT * FROM contacts WHERE name = ? var %result = $sqlite_query(%db, %sql, Joe Gibson)

  This is recommended way of inserting user data in queries, as it's the most clean and the most secure way to do it, and prevents the chance of SQL injections completely. Again, you can use /noop if you just want to execute a query, but don't care for the return value.
  
  All unbound parameters will be substituted with NULL when a query is executed.
• Output binding
  The last type of binding is output binding. With output binding, you can fetch data from a result set directly into variables using sqlite_fetch_bound or sqlite_current_bound. To bind an output variable you use sqlite_bind_field or its alias sqlite_bind_column. You can bind column names using its numeric index, or its name. This is very straightforward, so here's an example to demonstrate the usage:

  Code var %sql = SELECT name, email, tel FROM contacts var %result = $sqlite_query(%db, %sql) ; Bind using column index sqlite_bind_field %result 1 contact.name sqlite_bind_field %result 3 contact.tel ; Bind using column name sqlite_bind_field %result email contact.email ; Fetch the contacts while ($sqlite_fetch_bound(%result)) {   echo -a Name: %contact.name - Email: %contact.email - Tel: %contact.tel }

  You can also bind columns to binary variables, by prefixing the variable name with an ampersand as usual with binary variables. If you bind column with a binary data to a regular variable, the binary data will be converted into text and assigned to the variable. Similiarly if you bind a non-binary data to a binary variable, the binary variable will contain ASCII representation of the data.

There are a few things you need to be aware when binding parameters. Here's a list of some things you should know.

• Do not escape strings yourself
  SQLite will do it for you. In non-prepared statements, you generally have to use $sqlite_escape_string to escape special characters in strings to prevent SQL injection. With prepared statements there's no need for this.
• You can not bind values inside text
  Parameter binding has no effect inside strings. For example you can't do this:

  Code var %sql = SELECT * FROM contacts WHERE name LIKE '?@%' var %stmt = $sqlite_prepare(%db, %sql) sqlite_bind_value %stmt 1 joe

  Instead, you should be doing this:

  Code var %sql = SELECT * FROM contacts WHERE name LIKE ? var %stmt = $sqlite_prepare(%db, %sql) sqlite_bind_value %stmt 1 joe@%

• Be extra cautious with variable names when using sqlite_bind_field
  Because mSQLite has no access to local variables of aliases that mSQLite is being used from, it must bind variables bound with sqlite_bind_field as global variables. Because of this, you must be very careful with the variable names you use in order not to overwrite variables from other scripts. It's a good idea to prefix your variables in order to avoid name conflicts. Example:

  Code var %sql = SELECT name, email FROM contacts var %result = $sqlite_query(%db, %sql) sqlite_bind_field %result name contacts.name ; %contacts.name sqlite_bind_field %result email contacts.email ; %contacts.email

• Use $sqlite_bind_value instead of /sqlite_bind_value when the value might contain spaces
  You must use the identifier form of sqlite_bind_value when the data that is being bound might contain spaces. The reason is that the function contains another parameter after the value to be bound, and mIRC doesn't know how to deal with arguments containing more than one word when used as command. An example:

  Code ; Don't do this! sqlite_bind_value %stmt 1 Joe Johnson ; Do this! noop $sqlite_bind_value(%stmt, 1, Joe Johnson)