In
database management system
In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases span ...
s (DBMS), a prepared statement, parameterized statement, or parameterized query is a feature used to pre-compile
SQL code, separating it from data. Benefits of prepared statements are:
* efficiency, because they can be used repeatedly without re-compiling
* security, by reducing or eliminating
SQL injection
In computing, SQL injection is a code injection technique used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker). SQL inj ...
attacks
A prepared statement takes the form of a pre-compiled
template
Template may refer to:
Tools
* Die (manufacturing), used to cut or shape material
* Mold, in a molding process
* Stencil, a pattern or overlay used in graphic arts (drawing, painting, etc.) and sewing to replicate letters, shapes or designs
Co ...
into which constant values are substituted during each execution, and typically use
SQL DML statements such as
INSERT Insert may refer to:
*Insert (advertising)
*Insert (composites)
*Insert (effects processing)
*Insert (filmmaking)
*Insert key on a computer keyboard, used to switch between insert mode and overtype mode
*Insert (molecular biology)
*Insert (SQL)
*Fi ...
,
SELECT, or
UPDATE.
A common workflow for prepared statements is:
# Prepare: The application creates the statement template and sends it to the DBMS. Certain values are left unspecified, called ''parameters'', ''placeholders'' or ''bind variables'' (labelled "?" below):
#:
# Compile: The DBMS compiles (parses,
optimizes and translates) the statement template, and stores the result without executing it.
# Execute: The application supplies (or ''binds'') values for the parameters of the statement template, and the DBMS executes the statement (possibly returning a result). The application may request the DBMS to execute the statement many times with different values. In the above example, the application might supply the values "bike" for the first parameter and "10900" for the second parameter, and then later the values "shoes" and "7400".
The alternative to a prepared statement is calling SQL directly from the application source code in a way that combines code and data. The direct equivalent to the above example is:
INSERT INTO products (name, price) VALUES ("bike", "10900");
Not all optimization can be performed at the time the statement template is compiled, for two reasons: the best plan may depend on the specific values of the parameters, and the best plan may change as tables and indexes change over time.
On the other hand, if a query is executed only once, server-side prepared statements can be slower because of the additional round-trip to the server. Implementation limitations may also lead to performance penalties; for example, some versions of MySQL did not cache results of prepared queries.
A
stored procedure
A stored procedure (also termed proc, storp, sproc, StoPro, StoredProc, StoreProc, sp, or SP) is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database data dic ...
, which is also precompiled and stored on the server for later execution, has similar advantages. Unlike a stored procedure, a prepared statement is not normally written in a procedural language and cannot use or modify variables or use control flow structures, relying instead on the declarative database query language. Due to their simplicity and client-side emulation, prepared statements are more portable across vendors.
Software support
Major
DBMS
In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases spa ...
s, including
SQLite
SQLite (, ) is a database engine written in the C programming language. It is not a standalone app; rather, it is a library that software developers embed in their apps. As such, it belongs to the family of embedded databases. It is the most ...
,
MySQL
MySQL () is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter My, and "SQL", the acronym for Structured Query Language. A relational database o ...
,
Oracle
An oracle is a person or agency considered to provide wise and insightful counsel or prophetic predictions, most notably including precognition of the future, inspired by deities. As such, it is a form of divination.
Description
The word '' ...
,
IBM Db2
Db2 is a family of data management products, including database servers, developed by IBM. It initially supported the relational model, but was extended to support object–relational features and non-relational structures like JSON a ...
,
Microsoft SQL Server
Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which ma ...
and
PostgreSQL
PostgreSQL (, ), also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the In ...
support prepared statements. Prepared statements are normally executed through a non-SQL binary protocol for efficiency and protection from SQL injection, but with some DBMSs such as MySQL prepared statements are also available using a SQL syntax for debugging purposes.
A number of programming languages support prepared statements in their standard libraries and will emulate them on the client side even if the underlying DBMS does not support them, including
Java
Java (; id, Jawa, ; jv, ꦗꦮ; su, ) is one of the Greater Sunda Islands in Indonesia. It is bordered by the Indian Ocean to the south and the Java Sea to the north. With a population of 151.6 million people, Java is the world's List ...
's
JDBC
Java Database Connectivity (JDBC) is an application programming interface (API) for the programming language Java, which defines how a client may access a database. It is a Java-based data access technology used for Java database connectivity. I ...
,
Perl
Perl is a family of two high-level, general-purpose, interpreted, dynamic programming languages. "Perl" refers to Perl 5, but from 2000 to 2019 it also referred to its redesigned "sister language", Perl 6, before the latter's name was offici ...
's
DBI
The decibel (symbol: dB) is a relative unit of measurement equal to one tenth of a bel (B). It expresses the ratio of two values of a power or root-power quantity on a logarithmic scale. Two signals whose levels differ by one decibel have a p ...
,
PHP
PHP is a general-purpose scripting language geared toward web development. It was originally created by Danish-Canadian programmer Rasmus Lerdorf in 1993 and released in 1995. The PHP reference implementation is now produced by The PHP Group ...
's
PDO and
Python
Python may refer to:
Snakes
* Pythonidae, a family of nonvenomous snakes found in Africa, Asia, and Australia
** ''Python'' (genus), a genus of Pythonidae found in Africa and Asia
* Python (mythology), a mythical serpent
Computing
* Python (pro ...
's DB-API. Client-side emulation can be faster for queries which are executed only once, by reducing the number of round trips to the server, but is usually slower for queries executed many times. It resists SQL injection attacks equally effectively.
Many types of SQL injection attacks can be eliminated by ''disabling literals'', effectively requiring the use of prepared statements; only
H2 supports this feature.
[
]
Examples
Java JDBC
This example uses
Java
Java (; id, Jawa, ; jv, ꦗꦮ; su, ) is one of the Greater Sunda Islands in Indonesia. It is bordered by the Indian Ocean to the south and the Java Sea to the north. With a population of 151.6 million people, Java is the world's List ...
and
JDBC
Java Database Connectivity (JDBC) is an application programming interface (API) for the programming language Java, which defines how a client may access a database. It is a Java-based data access technology used for Java database connectivity. I ...
:
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Main
Java
PreparedStatement
provides "setters" (
setInt(int), setString(String), setDouble(double),
etc.) for all major built-in data types.
PHP PDO
This example uses
PHP
PHP is a general-purpose scripting language geared toward web development. It was originally created by Danish-Canadian programmer Rasmus Lerdorf in 1993 and released in 1995. The PHP reference implementation is now produced by The PHP Group ...
and
PDO:
Perl DBI
This example uses Perl
Perl is a family of two high-level, general-purpose, interpreted, dynamic programming languages. "Perl" refers to Perl 5, but from 2000 to 2019 it also referred to its redesigned "sister language", Perl 6, before the latter's name was offici ...
and DBI
The decibel (symbol: dB) is a relative unit of measurement equal to one tenth of a bel (B). It expresses the ratio of two values of a power or root-power quantity on a logarithmic scale. Two signals whose levels differ by one decibel have a p ...
:
#!/usr/bin/perl -w
use strict;
use DBI;
my ($db_name, $db_user, $db_password) = ('my_database', 'moi', 'Passw0rD');
my $dbh = DBI->connect("DBI:mysql:database=$db_name", $db_user, $db_password,
)
or die "ERROR (main:DBI->connect) while connecting to database $db_name: " .
$DBI::errstr . "\n";
$dbh->do('CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)');
my $sth = $dbh->prepare('INSERT INTO products VALUES (?, ?)');
$sth->execute(@$_) foreach bike', 10900 shoes', 7400 phone', 29500
$sth = $dbh->prepare("SELECT * FROM products WHERE name = ?");
$sth->execute('shoes');
print "$$_ n" foreach $sth->fetchrow_arrayref;
$sth->finish;
$dbh->disconnect;
C# ADO.NET
This example uses C# and ADO.NET:
using (SqlCommand command = connection.CreateCommand())
ADO.NET SqlCommand
will accept any type for the value
parameter of AddWithValue
, and type conversion occurs automatically. Note the use of "named parameters" (i.e. "@username"
) rather than "?"
—this allows you to use a parameter multiple times and in any arbitrary order within the query command text.
However, the AddWithValue method should not be used with variable length data types, like varchar and nvarchar. This is because .NET assumes the length of the parameter to be the length of the given value, rather than getting the actual length from the database via reflection. The consequence of this is that a different query plan is compiled and stored for each different length. In general, the maximum number of "duplicate" plans is the product of the lengths of the variable length columns as specified in the database. For this reason, it is important to use the standard Add method for variable length columns:
{{code, lang=csharp, 1=command.Parameters.Add(ParamName, VarChar, ParamLength).Value = ParamValue, where ParamLength is the length as specified in the database.
Since the standard Add method needs to be used for variable length data types, it is a good habit to use it for all parameter types.
Python DB-API
This example uses Python
Python may refer to:
Snakes
* Pythonidae, a family of nonvenomous snakes found in Africa, Asia, and Australia
** ''Python'' (genus), a genus of Pythonidae found in Africa and Asia
* Python (mythology), a mythical serpent
Computing
* Python (pro ...
and DB-API:
import mysql.connector
with mysql.connector.connect(database="mysql", user="root") as conn:
with conn.cursor(prepared=True) as cursor:
cursor.execute("CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)")
params = "bike", 10900),
("shoes", 7400),
("phone", 29500) cursor.executemany("INSERT INTO products VALUES (%s, %s)", params)
params = ("shoes",)
cursor.execute("SELECT * FROM products WHERE name = %s", params)
print(cursor.fetchall() 1])
Magic Direct SQL
This example uses Direct SQL from 4GL, Fourth generation language like eDeveloper, uniPaaS and magic XPA from Magic Software Enterprises
Magic Software Enterprises Ltd is a global enterprise software company headquartered in Or Yehuda, Israel. It is listed on the NASDAQ Global Select (NASDAQ: MGIC) and is also listed on the Tel Aviv Stock Exchange TA-100 Index.
History
Magic ...
Virtual username Alpha 20 init: 'sister'
Virtual password Alpha 20 init: 'yellow'
SQL Command: SELECT * FROM users WHERE USERNAME=:1 AND PASSWORD=:2
Input Arguments:
1: username
2: password
PureBasic
PureBasic
PureBasic is a commercially distributed procedural computer programming language and integrated development environment based on BASIC and developed by Fantaisie Software for Windows, Linux, and macOS. An Amiga version is available, although ...
(since v5.40 LTS) can manage 7 types of link with the following commands
SetDatabaseBlob, SetDatabaseDouble, SetDatabaseFloat, SetDatabaseLong, SetDatabaseNull, SetDatabaseQuad, SetDatabaseString
There are 2 different methods depending on the type of database
For SQLite, ODBC, MariaDB/Mysql use: ?
SetDatabaseString(#Database, 0, "test")
If DatabaseQuery(#Database, "SELECT * FROM employee WHERE id=?")
; ...
EndIf
For PostgreSQL use: $1, $2, $3, ...
SetDatabaseString(#Database, 0, "Smith") ; -> $1
SetDatabaseString(#Database, 1, "Yes") ; -> $2
SetDatabaseLong (#Database, 2, 50) ; -> $3
If DatabaseQuery(#Database, "SELECT * FROM employee WHERE id=$1 AND active=$2 AND years>$3")
; ...
EndIf
See also
* Code injection
Code injection is the exploitation of a computer bug that is caused by processing invalid data. The injection is used by an attacker to introduce (or "inject") code into a vulnerable computer program and change the course of execution. The res ...
References
Databases
SQL
Articles with example code