Indatabase management systems (DBMS), aprepared statement,parameterized statement, (not to be confused withparameterized query) is a feature where the database pre-compilesSQL code and stores the results, separating it from data. Benefits of prepared statements are:[1]
A prepared statement takes the form of a pre-compiledtemplate into which constant values are substituted during each execution, and typically useSQL DML statements such asINSERT,SELECT, orUPDATE.
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] Astored 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.
MajorDBMSs, includingSQLite,[5]MySQL,[6]Oracle,[7]IBM Db2,[8]Microsoft SQL Server[9] andPostgreSQL[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, includingJava'sJDBC,[12]Perl'sDBI,[13]PHP'sPDO[1] andPython'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 bydisabling literals, effectively requiring the use of prepared statements; as of 2007[update] onlyH2 supports this feature.[15]
// Define a BookModel type which wraps a sql.DB connection pool.typeBookModelstruct{DB*sql.DB}// This will insert a new book into the database.func(m*BookModel)Insert(title,authorstring)(int,error){stmt:="INSERT INTO book (title, author, created) VALUES(?, ?, UTC_TIMESTAMP())"result,err:=m.DB.Exec(stmt,title,author)iferr!=nil{return0,err}id,err:=result.LastInsertId()// Not support in postgress driveriferr!=nil{return0,err}// The ID returned has the type int64, so we convert it to an int type// before returning.returnint(id),nil}
The placeholder parameter syntax differs depending on your database. MySQL, SQL Server and SQLite use the ? notation, but PostgreSQL uses the $N notation. For example, if you were using PostgreSQL instead you would write:
_,err:=m.DB.Exec("INSERT INTO ... VALUES ($1, $2, $3)",...)
This example usesJava andJDBC:
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));}}}}
JavaPreparedStatement provides "setters" (setInt(int), setString(String), setDouble(double), etc.) for all major built-in data types.
This example usesPHP andPDO:[16]
<?php// Connect to a database named "mysql", with the password "root"$connection=newPDO('mysql:host=127.0.0.1;dbname=test;charset=utf8mb4','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}.";
#!/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 usesC# andADO.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.NETSqlCommand will accept any type for thevalue parameter ofAddWithValue, 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 usesPython 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 fromFourth generation language like eDeveloper, uniPaaS and magic XPA fromMagic Software Enterprises
Virtual username Alpha 20 init: 'sister'Virtual password Alpha 20 init: 'yellow'SQL Command:SELECT*FROMusersWHEREUSERNAME=:1ANDPASSWORD=:2Input Arguments: 1: username2: passwordPureBasic (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
ForSQLite,ODBC,MariaDB/Mysql use: ?
SetDatabaseString(#Database,0,"test")IfDatabaseQuery(#Database,"SELECT * FROM employee WHERE id=?"); ...EndIf
ForPostgreSQL use: $1, $2, $3, ...
SetDatabaseString(#Database,0,"Smith"); -> $1SetDatabaseString(#Database,1,"Yes"); -> $2SetDatabaseLong(#Database,2,50); -> $3IfDatabaseQuery(#Database,"SELECT * FROM employee WHERE id=$1 AND active=$2 AND years>$3"); ...EndIf