In database management systems (DBMS), a prepared statement, parameterized statement, or parameterized query is a feature where the database pre-compiles SQL code and stores the results, separating it from data. Benefits of prepared statements are: [1]
A prepared statement takes the form of a pre-compiled template into which constant values are substituted during each execution, and typically use SQL DML statements such as INSERT, SELECT, or UPDATE.
A common workflow for prepared statements is:
INSERTINTOproducts(name,price)VALUES(?,?);
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:
INSERTINTOproducts(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. [2]
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. [3] Implementation limitations may also lead to performance penalties; for example, some versions of MySQL did not cache results of prepared queries. [4] A stored procedure, 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.
Major DBMSs, including SQLite, [5] MySQL, [6] Oracle, [7] IBM Db2, [8] Microsoft SQL Server [9] and PostgreSQL [10] 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. [11]
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's JDBC, [12] Perl's DBI, [13] PHP's PDO [1] and Python's DB-API. [14] 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; as of 2007 [update] only H2 supports this feature. [15]
This example uses Java and JDBC:
importcom.mysql.jdbc.jdbc2.optional.MysqlDataSource;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;publicclassMain{publicstaticvoidmain(String[]args)throwsSQLException{MysqlDataSourceds=newMysqlDataSource();ds.setDatabaseName("mysql");ds.setUser("root");try(Connectionconn=ds.getConnection()){try(Statementstmt=conn.createStatement()){stmt.executeUpdate("CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)");}try(PreparedStatementstmt=conn.prepareStatement("INSERT INTO products VALUES (?, ?)")){stmt.setString(1,"bike");stmt.setInt(2,10900);stmt.executeUpdate();stmt.setString(1,"shoes");stmt.setInt(2,7400);stmt.executeUpdate();stmt.setString(1,"phone");stmt.setInt(2,29500);stmt.executeUpdate();}try(PreparedStatementstmt=conn.prepareStatement("SELECT * FROM products WHERE name = ?")){stmt.setString(1,"shoes");ResultSetrs=stmt.executeQuery();rs.next();System.out.println(rs.getInt(2));}}}}
Java PreparedStatement
provides "setters" (setInt(int), setString(String), setDouble(double),
etc.) for all major built-in data types.
This example uses PHP and PDO:
<?phptry{// Connect to a database named "mysql", with the password "root"$connection=newPDO('mysql:dbname=mysql','root');// Execute a request on the connection, which will create// a table "products" with two columns, "name" and "price"$connection->exec('CREATE TABLE IF NOT EXISTS products (name VARCHAR(40), price INT)');// Prepare a query to insert multiple products into the table$statement=$connection->prepare('INSERT INTO products VALUES (?, ?)');$products=[['bike',10900],['shoes',7400],['phone',29500],];// Iterate through the products in the "products" array, and// execute the prepared statement for each productforeach($productsas$product){$statement->execute($product);}// Prepare a new statement with a named parameter$statement=$connection->prepare('SELECT * FROM products WHERE name = :name');$statement->execute([':name'=>'shoes',]);// Use array destructuring to assign the product name and its price// to corresponding variables[$product,$price]=$statement->fetch();// Display the result to the userecho"The price of the product {$product} is \${$price}.";// Close the cursor so `fetch` can eventually be used again$statement->closeCursor();}catch(\Exception$e){echo'An error has occurred: '.$e->getMessage();}
This example uses Perl and DBI:
#!/usr/bin/perl -wusestrict;useDBI;my($db_name,$db_user,$db_password)=('my_database','moi','Passw0rD');my$dbh=DBI->connect("DBI:mysql:database=$db_name",$db_user,$db_password,{RaiseError=>1,AutoCommit=>1})ordie"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"$$_[1]\n"foreach$sth->fetchrow_arrayref;$sth->finish;$dbh->disconnect;
This example uses C# and ADO.NET:
using(SqlCommandcommand=connection.CreateCommand()){command.CommandText="SELECT * FROM users WHERE USERNAME = @username AND ROOM = @room";command.Parameters.AddWithValue("@username",username);command.Parameters.AddWithValue("@room",room);using(SqlDataReaderdataReader=command.ExecuteReader()){// ...}}
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:
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.
This example uses Python and DB-API:
importmysql.connectorwithmysql.connector.connect(database="mysql",user="root")asconn:withconn.cursor(prepared=True)ascursor: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()[0][1])
This example uses Direct SQL from Fourth generation language like eDeveloper, uniPaaS and magic XPA from Magic Software Enterprises
Virtual username Alpha 20 init: 'sister' Virtual password Alpha 20 init: 'yellow' SQL Command: SELECT*FROMusersWHEREUSERNAME=:1ANDPASSWORD=:2
Input Arguments: 1: username 2: password
PureBasic (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")IfDatabaseQuery(#Database,"SELECT * FROM employee WHERE id=?"); ...EndIf
For PostgreSQL use: $1, $2, $3, ...
SetDatabaseString(#Database,0,"Smith"); -> $1 SetDatabaseString(#Database,1,"Yes"); -> $2SetDatabaseLong(#Database,2,50); -> $3IfDatabaseQuery(#Database,"SELECT * FROM employee WHERE id=$1 AND active=$2 AND years>$3"); ...EndIf
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 organizes data into one or more data tables in which data may be related to each other; these relations help structure the data. SQL is a language that programmers use to create, modify and extract data from the relational database, as well as control user access to the database. In addition to relational databases and SQL, an RDBMS like MySQL works with an operating system to implement a relational database in a computer's storage system, manages users, allows for network access and facilitates testing database integrity and creation of backups.
Structured Query Language (SQL) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e., data incorporating relations among entities and variables.
An object–relational database (ORD), or object–relational database management system (ORDBMS), is a database management system (DBMS) similar to a relational database, but with an object-oriented database model: objects, classes and inheritance are directly supported in database schemas and in the query language. In addition, just as with pure relational systems, it supports extension of the data model with custom data types and methods.
A stored procedure is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database data dictionary.
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. SQL injection must exploit a security vulnerability in an application's software, for example, when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and unexpectedly executed. SQL injection is mostly known as an attack vector for websites but can be used to attack any type of SQL database.
In computing, a data source name is a string that has an associated data structure used to describe a connection to a data source. Most commonly used in connection with ODBC, DSNs also exist for JDBC and for other data access mechanisms. The term often overlaps with "connection string". Most systems do not make a distinction between DSNs or connection strings and the term can often be used interchangeably.
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 code into a vulnerable computer program and change the course of execution. The result of successful code injection can be disastrous, for example, by allowing computer viruses or computer worms to propagate.
A query plan is a sequence of steps used to access data in a SQL relational database management system. This is a specific case of the relational model concept of access plans.
A user-defined function (UDF) is a function provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment. UDFs are usually written for the requirement of its creator.
Taint checking is a feature in some computer programming languages, such as Perl, Ruby or Ballerina designed to increase security by preventing malicious users from executing commands on a host computer. Taint checks highlight specific security risks primarily associated with web sites which are attacked using techniques such as SQL injection or buffer overflow attack approaches.
iBATIS is a persistence framework which automates the mapping between SQL databases and objects in Java, .NET, and Ruby on Rails. In Java, the objects are POJOs. The mappings are decoupled from the application logic by packaging the SQL statements in XML configuration files. The result is a significant reduction in the amount of code that a developer needs to access a relational database using lower level APIs like JDBC and ODBC.
Judoscript is a general purpose programming language designed primarily for scripting tasks on the Java platform. It was conceived and developed by James Jianbo Huang, starting in late 2001. Judoscript was one of the first so-called Java scripting languages; but its most striking characteristics is its audacious multi-domain support philosophy and practice.
The MySQLi Extension is a relational database driver used in the PHP scripting language to provide an interface with MySQL databases.
Apache Empire-db is a Java library that provides a high level object-oriented API for accessing relational database management systems (RDBMS) through JDBC. Apache Empire-db is open source and provided under the Apache License 2.0 from the Apache Software Foundation.
XLeratorDB is a suite of database function libraries that enable Microsoft SQL Server to perform a wide range of additional (non-native) business intelligence and ad hoc analytics. The libraries, which are embedded and run centrally on the database, include more than 450 individual functions similar to those found in Microsoft Excel spreadsheets. The individual functions are grouped and sold as six separate libraries based on usage: finance, statistics, math, engineering, unit conversions and strings. WestClinTech, the company that developed XLeratorDB, claims it is "the first commercial function package add-in for Microsoft SQL Server."
Java Database Connectivity (JDBC) is an application programming interface (API) for the Java programming language which defines how a client may access a database. It is a Java-based data access technology used for Java database connectivity. It is part of the Java Standard Edition platform, from Oracle Corporation. It provides methods to query and update data in a database, and is oriented toward relational databases. A JDBC-to-ODBC bridge enables connections to any ODBC-accessible data source in the Java virtual machine (JVM) host environment.
MyBatis is a Java persistence framework that couples objects with stored procedures or SQL statements using an XML descriptor or annotations.
PL/SQL is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database, Times Ten in-memory database, and IBM Db2. Oracle Corporation usually extends PL/SQL functionality with each successive release of the Oracle Database.
Yesod is a web framework based on the programming language Haskell for productive development of type-safe, representational state transfer (REST) model based, high performance web applications, developed by Michael Snoyman, et al. It is free and open-source software released under an MIT License.
Persist is a Java-based ORM/DAO tool. It provides only the minimal amount of functionalities necessary to map objects or maps from database queries and to statement parameters.