Most people that have written programs using a database has encountered one or more of the problems.
Example:
sql = "INSERT INTO tt VALUES(" + id + ",'" + name + "')"
With id=123 and name="Smith" then it works fine for:
sql = "INSERT INTO tt VALUES(123,'Smith')"
But with id=123 and name="O'Toole" we get a SQL syntax error for:
sql = "INSERT INTO tt VALUES(123,'O'Toole')"
Example:
sql = "SELECT * FROM myusers WHERE un = '" + username + "' AND pw = '" + password + "'"
With username="arne" and password="secret" then it works fine for:
sql = "SELECT * FROM myusers WHERE un = 'arne' AND pw = 'secret'"
But with username="arne" and password="x' OR 'x' = 'x" we get a very bad result:
sql = "SELECT * FROM myusers WHERE un = 'arne' AND pw = 'x' OR 'x' = 'x'"
Date formats frequently gives problem with database.
What format to use?
and:
and what controls the format?
A beginner solution for the first two problems is to just double all single quotes in input.
Using above examples:
sql = "INSERT INTO tt VALUES(" + id + ",'" + replace(name,"'","''") + "')"
sql = "INSERT INTO tt VALUES(123,'Smith')"
sql = "INSERT INTO tt VALUES(123,'O''Toole')"
and:
sql = "SELECT * FROM myusers WHERE un = '" + username.replaceAll("'","''") + "' AND pw = '" + password.replaceAll("'","''") + "'"
sql = "SELECT * FROM myusers WHERE un = 'arne' AND pw = 'hemmeligt'"
sql = "SELECT * FROM myusers WHERE un = 'arne' AND pw = 'x'' OR ''x'' = ''x'"
And it sort of work, but:
The date format problem is often solved by hardcoding a database and configuration specific format matching the target system. That can cause some bad problems when database or configuration later change.
The right solution is to use prepared statement / parameters.
Some call it prepared statement:
Some call it parameters:
The concept is very simple. Instead of constructing the SQL statement to be executed by concatanating a number of strings, then a fixed SQL string with placeholders is used and values are set for those placeholders. And then the database handles all the problems.
The following test tables are used for the examples:
CREATE TABLE tt (
id INTEGER PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE myusers (
un VARCHAR(32) PRIMARY KEY,
pw VARCHAR(32)
);
INSERT INTO myusers VALUES('arne', 'hemmeligt');
CREATE TABLE dtest (
i INTEGER PRIMARY KEY,
d DATETIME
);
Insert with single quotes:
package testprep;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class TestPrep1 {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "arne", "xxxx");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO tt VALUES (?, ?)");
pstmt.setInt(1, 123);
pstmt.setString(2, "Smith");
pstmt.executeUpdate();
pstmt.setInt(1, 124);
pstmt.setString(2, "O'Toole");
pstmt.executeUpdate();
pstmt.close();
con.close();
}
}
using System;
using System.Data;
using System.Data.SqlClient;
namespace TestParam
{
public class TestClass1
{
public static void Main(string[] args)
{
SqlConnection con = new SqlConnection(@"Server=ARNEPC4\SQLEXPRESS;Integrated Security=SSPI;Database=Test");
con.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO tt VALUES (@id, @name)", con);
cmd.Parameters.Add("@id", SqlDbType.Int);
cmd.Parameters.Add("@name", SqlDbType.VarChar, 50);
cmd.Parameters["@id"].Value = 123;
cmd.Parameters["@name"].Value = "Smith";
cmd.ExecuteNonQuery();
cmd.Parameters["@id"].Value = 124;
cmd.Parameters["@name"].Value = "O'Toole";
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Dispose();
}
}
}
Imports System
Imports System.Data
Imports System.Data.SqlClient
Namespace TestParam
Public Class TestClass1
Public Shared Sub Main(args As String())
Dim con As New SqlConnection("Server=ARNEPC4\SQLEXPRESS;Integrated Security=SSPI;Database=Test")
con.Open()
Dim cmd As New SqlCommand("INSERT INTO tt VALUES (@id, @name)", con)
cmd.Parameters.Add("@id", SqlDbType.Int)
cmd.Parameters.Add("@name", SqlDbType.VarChar, 50)
cmd.Parameters("@id").Value = 123
cmd.Parameters("@name").Value = "Smith"
cmd.ExecuteNonQuery()
cmd.Parameters("@id").Value = 124
cmd.Parameters("@name").Value = "O'Toole"
cmd.ExecuteNonQuery()
cmd.Dispose()
con.Dispose()
End Sub
End Class
End Namespace
<?php
$con = new mysqli('localhost', 'root', '', 'Test');
$stmt = $con->prepare('INSERT INTO tt VALUES (?, ?)');
$stmt->bind_param('is', $id, $name);
$id = 123;
$name = 'Smith';
$stmt->execute();
$id = 124;
$name = "O'Toole";
$stmt->execute();
$stmt->close();
$con->close();
?>
Note that I am not really a PHP programmer so the above may not be optimal code.
<?php
$con = new PDO('mysql:host=localhost;dbname=Test','root', '');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $con->prepare('INSERT INTO tt VALUES (:id, :name)');
$stmt->execute(array(':id' => 123, ':name' => 'Smith'));
$stmt->execute(array(':id' => 124, ':name' => "O'Toole"));
?>
Note that I am not really a PHP programmer so the above may not be optimal code.
<%
adParamInput = 1
adInteger = 3
adVarChar = 200
Set con = Server.CreateObject("ADODB.Connection")
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Work\Database11.accdb;User Id=admin;Password=;"
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = con
cmd.CommandText = "INSERT INTO tt VALUES(@id,@name)"
cmd.Parameters.Append(cmd.CreateParameter("@id", adInteger, adParamInput))
cmd.Parameters.Append(cmd.CreateParameter("@name", adVarChar, adParamInput, 50))
cmd.Parameters("@id") = 123
cmd.Parameters("@name") = "Smith"
cmd.Execute
cmd.Parameters("@id") = 124
cmd.Parameters("@name") = "O'Toole"
cmd.Execute
Set cmd = Nothing
Set con = Nothing
%>
Note that I am not really an ASP programmer so the above may not be optimal code.
Note that we do *not* use '' around placeholders for strings.
Note that in real code error handling should be addded.
Select and SQL injection:
package testprep;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestPrep2 {
public static boolean isValid(String un, String pw) throws SQLException {
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "arne", "xxxx");
PreparedStatement pstmt = con.prepareStatement("SELECT * FROM myusers WHERE un = ? AND pw = ?");
pstmt.setString(1, un);
pstmt.setString(2, pw);
ResultSet rs = pstmt.executeQuery();
boolean res = rs.next();
rs.close();
pstmt.close();
con.close();
return res;
}
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.OracleDriver");
System.out.println(isValid("anonymous", ""));
System.out.println(isValid("arne", "hemmeligt"));
System.out.println(isValid("arne", "x' OR 'x' = 'x"));
}
}
using System;
using System.Data;
using System.Data.SqlClient;
namespace TestParam
{
public class TestClass2
{
public static bool IsValid(string un, string pw)
{
SqlConnection con = new SqlConnection(@"Server=ARNEPC4\SQLEXPRESS;Integrated Security=SSPI;Database=Test");
con.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM myusers WHERE un = @un AND pw = @pw", con);
cmd.Parameters.Add("@un", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@pw", SqlDbType.VarChar, 50);
cmd.Parameters["@un"].Value = un;
cmd.Parameters["@pw"].Value = pw;
SqlDataReader rdr = cmd.ExecuteReader();
bool res = rdr.Read();
rdr.Dispose();
cmd.Dispose();
con.Dispose();
return res;
}
public static void Main(string[] args)
{
Console.WriteLine(IsValid("anonymous", ""));
Console.WriteLine(IsValid("arne", "hemmeligt"));
Console.WriteLine(IsValid("arne", "x' OR 'x' = 'x"));
}
}
}
Imports System
Imports System.Data
Imports System.Data.SqlClient
Namespace TestParam
Public Class TestClass2
Public Shared Function IsValid(un As String, pw As String) As Boolean
Dim con As New SqlConnection("Server=ARNEPC4\SQLEXPRESS;Integrated Security=SSPI;Database=Test")
con.Open()
Dim cmd As New SqlCommand("SELECT * FROM myusers WHERE un = @un AND pw = @pw", con)
cmd.Parameters.Add("@un", SqlDbType.VarChar, 50)
cmd.Parameters.Add("@pw", SqlDbType.VarChar, 50)
cmd.Parameters("@un").Value = un
cmd.Parameters("@pw").Value = pw
Dim rdr As SqlDataReader = cmd.ExecuteReader()
Dim res As Boolean = rdr.Read()
rdr.Dispose()
cmd.Dispose()
con.Dispose()
Return res
End Function
Public Shared Sub Main(args As String())
Console.WriteLine(IsValid("anonymous", ""))
Console.WriteLine(IsValid("arne", "hemmeligt"))
Console.WriteLine(IsValid("arne", "x' OR 'x' = 'x"))
End Sub
End Class
End Namespace
<?php
function isvalid($un, $pw) {
$con = new mysqli('localhost', 'root', '', 'Test');
$stmt = $con->prepare('SELECT * FROM myusers WHERE un = ? AND pw = ?');
$stmt->bind_param('ss', $un, $pw);
$stmt->execute();
$rs = $stmt->get_result();
$res = $rs->fetch_array() != false;
$rs->close();
$stmt->close();
$con->close();
return $res;
}
function xisvalid($un, $pw) {
return isvalid($un, $pw) ? "OK" : "not OK";
}
echo xisvalid('anonymous', '') . "\r\n";
echo xisvalid('arne', 'hemmeligt') . "\r\n";
echo xisvalid('arne', "x' OR 'x' = 'x") . "\r\n";
>
Note that I am not really a PHP programmer so the above may not be optimal code.
<?php
function isvalid($un, $pw) {
$con = new PDO('mysql:host=localhost;dbname=Test','root', '');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $con->prepare('SELECT * FROM myusers WHERE un = :un AND pw = :pw');
$stmt->execute(array(':un' => $un, ':pw' => $pw));
$res = $stmt->fetch() != false;
return $res;
}
function xisvalid($un, $pw) {
return isvalid($un, $pw) ? "OK" : "not OK";
}
echo xisvalid('anonymous', '') . "\r\n";
echo xisvalid('arne', 'hemmeligt') . "\r\n";
echo xisvalid('arne', "x' OR 'x' = 'x") . "\r\n";
?>
Note that I am not really a PHP programmer so the above may not be optimal code.
<%
adParamInput = 1
adVarChar = 200
Function isvalid(un,pw)
Set con = Server.CreateObject("ADODB.Connection")
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Work\Database11.accdb;User Id=admin;Password=;"
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = con
cmd.CommandText = "SELECT * FROM myusers WHERE username = @un AND password = @pw"
cmd.Parameters.Append(cmd.CreateParameter("@un", adVarChar, adParamInput, 50))
cmd.Parameters.Append(cmd.CreateParameter("@pw", adVarChar, adParamInput, 50))
cmd.Parameters("@un") = un
cmd.Parameters("@pw") = pw
Set rs = cmd.Execute
res = Not rs.EOF
Set rs = Nothing
Set cmd = Nothing
Set con = Nothing
isvalid = res
End Function
%>
<%=isvalid("anonymous","")%>
<BR>
<%=isvalid("arne","hemmelig")%>
<BR>
<%=isvalid("arne","x' OR 'x' = 'x")%>
Note that I am not really an ASP programmer so the above may not be optimal code.
Note that in real code error handling should be addded.
And insert with date:
package testprep;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.Calendar;
public class TestPrep3 {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "arne", "xxxx");
PreparedStatement ins = con.prepareStatement("INSERT INTO dtest VALUES (?, ?)");
for(int i = 0; i < 10; i++) {
ins.setInt(1, i);
Timestamp ts = new Timestamp(Calendar.getInstance().getTimeInMillis());
ins.setTimestamp(2, ts);
ins.executeUpdate();
Thread.sleep(1000);
}
ins.close();
PreparedStatement sel = con.prepareStatement("SELECT * FROM dtest WHERE d > ?");
Timestamp cut = new Timestamp(Calendar.getInstance().getTimeInMillis() - 5000);
sel.setTimestamp(1, cut);
ResultSet rs = sel.executeQuery();
while(rs.next()) {
int i = rs.getInt(1);
Timestamp ts = rs.getTimestamp(2);
System.out.println(i + " " + ts);
}
rs.close();
sel.close();
con.close();
}
}
using System;
using System.Threading;
using System.Data;
using System.Data.SqlClient;
namespace TestParam
{
public class TestClass3
{
public static void Main(string[] args)
{
SqlConnection con = new SqlConnection(@"Server=ARNEPC4\SQLEXPRESS;Integrated Security=SSPI;Database=Test");
con.Open();
SqlCommand ins = new SqlCommand("INSERT INTO dtest VALUES (@i, @d)", con);
ins.Parameters.Add("@i", SqlDbType.Int);
ins.Parameters.Add("@d", SqlDbType.DateTime);
for(int i = 0; i < 10; i++)
{
ins.Parameters["@i"].Value = i;
ins.Parameters["@d"].Value = DateTime.Now;
ins.ExecuteNonQuery();
Thread.Sleep(1000);
}
ins.Dispose();
SqlCommand sel = new SqlCommand("SELECT * FROM dtest WHERE d > @d", con);
sel.Parameters.Add("@d", SqlDbType.DateTime);
DateTime cut = DateTime.Now.AddSeconds(-5);
sel.Parameters["@d"].Value = cut;
SqlDataReader rdr = sel.ExecuteReader();
while(rdr.Read())
{
int i = (int)rdr[0];
DateTime dt = (DateTime)rdr[1];
Console.WriteLine(i + " " + dt);
}
sel.Dispose();
rdr.Dispose();
con.Dispose();
}
}
}
Imports System
Imports System.Threading
Imports System.Data
Imports System.Data.SqlClient
Namespace TestParam
Public Class TestClass3
Public Shared Sub Main(args As String())
Dim con As New SqlConnection("Server=ARNEPC4\SQLEXPRESS;Integrated Security=SSPI;Database=Test")
con.Open()
Dim ins As New SqlCommand("INSERT INTO dtest VALUES (@i, @d)", con)
ins.Parameters.Add("@i", SqlDbType.Int)
ins.Parameters.Add("@d", SqlDbType.DateTime)
For i As Integer = 0 To 9
ins.Parameters("@i").Value = i
ins.Parameters("@d").Value = DateTime.Now
ins.ExecuteNonQuery()
Thread.Sleep(1000)
Next
ins.Dispose()
Dim sel As New SqlCommand("SELECT * FROM dtest WHERE d > @d", con)
sel.Parameters.Add("@d", SqlDbType.DateTime)
Dim cut As DateTime = DateTime.Now.AddSeconds(-5)
sel.Parameters("@d").Value = cut
Dim rdr As SqlDataReader = sel.ExecuteReader()
While rdr.Read()
Dim i As Integer = CInt(rdr(0))
Dim dt As DateTime = CType(rdr(1), DateTime)
Console.WriteLine(i & " " & dt)
End While
sel.Dispose()
rdr.Dispose()
con.Dispose()
End Sub
End Class
End Namespace
PHP mysqli does not support datetime directly. For DATETIME column use s format and supply a string in format 'Y-m-d H:i:s'. For TIMESTAMP column use i format and supply an integer.
<?php
$con = new mysqli('localhost', 'root', '', 'Test');
$ins = $con->prepare('INSERT INTO dtest VALUES (?, ?)');
$ins->bind_param('is', $iv, $dv);
for($i = 0; $i < 10; $i++) {
$iv = $i;
$dv = date('Y-m-d H:i:s');
$ins->execute();
sleep(1);
}
$ins->close();
$sel = $con->prepare('SELECT * FROM dtest WHERE d > ?');
$sel->bind_param('s', $cut);
$cut = date('Y-m-d H:i:s', time() - 5);
$sel->execute();
$rs = $sel->get_result();
while($row = $rs->fetch_array()) {
$iv = $row['i'];
$dv = $row['d'];
echo "$iv $dv\r\n";
}
$rs->close();
$sel->close();
$con->close();
?>
Note that I am not really a PHP programmer so the above may not be optimal code.
PHP PDO does not support datetime directly. For DATETIME column supply a string in format 'Y-m-d H:i:s'. For TIMESTAMP column supply an integer.
<?php
$con = new PDO('mysql:host=localhost;dbname=Test','root', '');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$ins = $con->prepare('INSERT INTO dtest VALUES (:i, :d)');
for($i = 0; $i < 10; $i++) {
$iv = $i;
$dv = date('Y-m-d H:i:s');
$ins->execute(array(':i' => $iv, ':d' => $dv));
sleep(1);
}
$sel = $con->prepare('SELECT * FROM dtest WHERE d > :d');
$cut = date('Y-m-d H:i:s', time() - 5);
$sel->execute(array(':d' => $cut));
while($row = $sel->fetch()) {
$iv = $row['i'];
$dv = $row['d'];
echo "$iv $dv\r\n";
}
?>
Note that I am not really a PHP programmer so the above may not be optimal code.
<%
adParamInput = 1
adInteger = 3
adDate = 7
' **** NEVER EVER USE THIS IN PRODUCTION ****
Function sleep(n)
timeout = Now() + (n / (24 * 60* 60))
Do While Now() < timeout
' do nothing
Loop
End Function
Set con = Server.CreateObject("ADODB.Connection")
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Work\Database11.accdb;User Id=admin;Password=;"
Set ins = Server.CreateObject("ADODB.Command")
ins.ActiveConnection = con
ins.CommandText = "INSERT INTO dtest VALUES(@i,@d)"
ins.Parameters.Append(ins.CreateParameter("@i", adInteger, adParamInput))
ins.Parameters.Append(ins.CreateParameter("@d", adDate, adParamInput))
For i = 0 To 9
ins.Parameters("@i").Value = i
ins.Parameters("@d").Value = Now()
ins.Execute
sleep(1)
Next
Set ins = Nothing
Set sel = Server.CreateObject("ADODB.Command")
sel.ActiveConnection = con
sel.CommandText = "SELECT * FROM dtest WHERE d > @d"
sel.Parameters.Append(sel.CreateParameter("@d", adDate, adParamInput))
cut = Now() - 6 / (24 * 60 * 60)
sel.Parameters("@d") = cut
Set rs = sel.Execute
Do While Not rs.EOF
Response.Write rs("i") & " " & rs("d") & "<BR>"
rs.MoveNext
Loop
Set rs = Nothing
Set sel = Nothing
Set con = Nothing
%>
Note that I am not really an ASP programmer so the above may not be optimal code.
Note that in real code error handling should be addded.
Depending on database and driver/provider/extension then using prepared statement / parameters can sometimes significantly improve performance, when doing the same operation many times.
Some random tests:
package testprep;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
public class TestPrep4 {
private static final int N = 10000;
private static void delete(Connection con) throws SQLException {
Statement stmt = con.createStatement();
stmt.executeUpdate("DELETE FROM tt");
stmt.close();
}
private static void testStatSing(Connection con) throws SQLException {
delete(con);
long t1 = System.currentTimeMillis();
for(int i = 0; i < N; i++) {
Statement stmt = con.createStatement();
String s = "Some text bla bla bla";
stmt.executeUpdate("INSERT INTO tt VALUES (" + i + ",'" + s + "')");
stmt.close();
}
long t2 = System.currentTimeMillis();
System.out.println("Statement not reused: " + (t2 - t1));
}
private static void testStatMulti(Connection con) throws SQLException {
delete(con);
long t1 = System.currentTimeMillis();
Statement stmt = con.createStatement();
for(int i = 0; i < N; i++) {
String s = "Some text bla bla bla";
stmt.executeUpdate("INSERT INTO tt VALUES (" + i + ",'" + s + "')");
}
stmt.close();
long t2 = System.currentTimeMillis();
System.out.println("Statement reused: " + (t2 - t1));
}
private static void testPrepStatSing(Connection con) throws SQLException {
delete(con);
long t1 = System.currentTimeMillis();
for(int i = 0; i < N; i++) {
PreparedStatement pstmt = con.prepareStatement("INSERT INTO tt VALUES (?, ?)");
pstmt.setInt(1, i);
pstmt.setString(2, "Some text bla bla bla");
pstmt.executeUpdate();
pstmt.close();
}
long t2 = System.currentTimeMillis();
System.out.println("PreparedStatement not reused: " + (t2 - t1));
}
private static void testPrepStatMulti(Connection con) throws SQLException {
delete(con);
long t1 = System.currentTimeMillis();
PreparedStatement pstmt = con.prepareStatement("INSERT INTO tt VALUES (?, ?)");
for(int i = 0; i < N; i++) {
pstmt.setInt(1, i);
pstmt.setString(2, "Some text bla bla bla");
pstmt.executeUpdate();
}
pstmt.close();
long t2 = System.currentTimeMillis();
System.out.println("PreparedStatement reused: " + (t2 - t1));
}
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "arne", "xxxx");
testStatSing(con);
testStatMulti(con);
testPrepStatSing(con);
testPrepStatMulti(con);
con.close();
}
}
Statement not reused: 5540 Statement reused: 5333 PreparedStatement not reused: 3764 PreparedStatement reused: 2527
using System;
using System.Data;
using System.Data.SqlClient;
namespace TestParam
{
public class TestClass4
{
private const int N = 10000;
private static void Delete(SqlConnection con)
{
using(SqlCommand cmd = new SqlCommand("DELETE FROM tt", con))
{
cmd.ExecuteNonQuery();
}
}
private static void TestNonParam(SqlConnection con)
{
Delete(con);
long t1 = DateTime.Now.Ticks;
for(int i = 0; i < N; i++)
{
string s = "Some text bla bla bla";
using(SqlCommand cmd = new SqlCommand("INSERT INTO tt VALUES (" + i + ",'" + s + "')", con))
{
cmd.ExecuteNonQuery();
}
}
long t2 = DateTime.Now.Ticks;
Console.WriteLine("Non parameters: " + (t2 - t1)/10000);
}
private static void TestNonParamSing(SqlConnection con)
{
Delete(con);
long t1 = DateTime.Now.Ticks;
for(int i = 0; i < N; i++)
{
using(SqlCommand cmd = new SqlCommand("INSERT INTO tt VALUES (@id,@name)", con))
{
cmd.Parameters.Add("@id", SqlDbType.Int);
cmd.Parameters.Add("@name", SqlDbType.VarChar, 50);
cmd.Parameters["@id"].Value = i;
cmd.Parameters["@name"].Value = "Some text bla bla bla";
cmd.ExecuteNonQuery();
}
}
long t2 = DateTime.Now.Ticks;
Console.WriteLine("Parameters not reused: " + (t2 - t1)/10000);
}
private static void TestNonParamMulti(SqlConnection con)
{
Delete(con);
long t1 = DateTime.Now.Ticks;
using(SqlCommand cmd = new SqlCommand("INSERT INTO tt VALUES (@id,@name)", con))
{
cmd.Parameters.Add("@id", SqlDbType.Int);
cmd.Parameters.Add("@name", SqlDbType.VarChar, 50);
for(int i = 0; i < N; i++)
{
cmd.Parameters["@id"].Value = i;
cmd.Parameters["@name"].Value = "Some text bla bla bla";
cmd.ExecuteNonQuery();
}
}
long t2 = DateTime.Now.Ticks;
Console.WriteLine("Parameters reused: " + (t2 - t1)/10000);
}
public static void Main(string[] args)
{
using(SqlConnection con = new SqlConnection(@"Server=ARNEPC4\SQLEXPRESS;Integrated Security=SSPI;Database=Test"))
{
con.Open();
TestNonParam(con);
TestNonParamSing(con);
TestNonParamMulti(con);
}
}
}
}
Non parameters: 5444 Parameters not reused: 5584 Parameters reused: 4992
Imports System
Imports System.Data
Imports System.Data.SqlClient
Namespace TestParam
Public Class TestClass4
Private Const N As Integer = 10000
Private Shared Sub Delete(con As SqlConnection)
Using cmd As New SqlCommand("DELETE FROM tt", con)
cmd.ExecuteNonQuery()
End Using
End Sub
Private Shared Sub TestNonParam(con As SqlConnection)
Delete(con)
Dim t1 As Long = DateTime.Now.Ticks
For i As Integer = 0 To N - 1
Dim s As String = "Some text bla bla bla"
Using cmd As New SqlCommand("INSERT INTO tt VALUES (" & i & ",'" & s & "')", con)
cmd.ExecuteNonQuery()
End Using
Next
Dim t2 As Long = DateTime.Now.Ticks
Console.WriteLine("Non parameters: " & (t2 - t1) \ 10000)
End Sub
Private Shared Sub TestNonParamSing(con As SqlConnection)
Delete(con)
Dim t1 As Long = DateTime.Now.Ticks
For i As Integer = 0 To N - 1
Using cmd As New SqlCommand("INSERT INTO tt VALUES (@id,@name)", con)
cmd.Parameters.Add("@id", SqlDbType.Int)
cmd.Parameters.Add("@name", SqlDbType.VarChar, 50)
cmd.Parameters("@id").Value = i
cmd.Parameters("@name").Value = "Some text bla bla bla"
cmd.ExecuteNonQuery()
End Using
Next
Dim t2 As Long = DateTime.Now.Ticks
Console.WriteLine("Parameters not reused: " & (t2 - t1) \ 10000)
End Sub
Private Shared Sub TestNonParamMulti(con As SqlConnection)
Delete(con)
Dim t1 As Long = DateTime.Now.Ticks
Using cmd As New SqlCommand("INSERT INTO tt VALUES (@id,@name)", con)
cmd.Parameters.Add("@id", SqlDbType.Int)
cmd.Parameters.Add("@name", SqlDbType.VarChar, 50)
For i As Integer = 0 To N - 1
cmd.Parameters("@id").Value = i
cmd.Parameters("@name").Value = "Some text bla bla bla"
cmd.ExecuteNonQuery()
Next
End Using
Dim t2 As Long = DateTime.Now.Ticks
Console.WriteLine("Parameters reused: " & (t2 - t1) \ 10000)
End Sub
Public Shared Sub Main(args As String())
Using con As New SqlConnection("Server=ARNEPC4\SQLEXPRESS;Integrated Security=SSPI;Database=Test")
con.Open()
TestNonParam(con)
TestNonParamSing(con)
TestNonParamMulti(con)
End Using
Console.ReadKey()
End Sub
End Class
End Namespace
Non parameters: 5444 Parameters not reused: 5584 Parameters reused: 4992
<?php
define('N',10000);
function delete($con) {
$con->query('DELETE FROM tt');
}
function test_non_prep($con) {
delete($con);
$t1 = microtime(true);
for($i = 0; $i < N; $i++) {
$s = 'Some text bla bla bla';
$stmt = $con->query("INSERT INTO tt VALUES ($i, '$s')");
}
$t2 = microtime(true);
$dt = (int)(($t2 - $t1) * 1000);
echo "Non prepared: $dt\r\n";
}
function test_prep_sing($con) {
$t1 = microtime(true);
for($i = 0; $i < N; $i++) {
$stmt = $con->prepare('INSERT INTO tt VALUES (?, ?)');
$s = 'Some text bla bla bla';
$stmt->bind_param('is', $i, $s);
$stmt->execute();
$stmt->close();
}
$t2 = microtime(true);
$dt = (int)(($t2 - $t1) * 1000);
echo "Prepared not reused: $dt\r\n";
}
function test_prep_multi($con) {
$t1 = microtime(true);
$stmt = $con->prepare('INSERT INTO tt VALUES (?, ?)');
for($i = 0; $i < N; $i++) {
$s = 'Some text bla bla bla';
$stmt->bind_param('is', $i, $s);
$stmt->execute();
}
$stmt->close();
$t2 = microtime(true);
$dt = (int)(($t2 - $t1) * 1000);
echo "Prepared reused: $dt\r\n";
}
$con = new mysqli('localhost', 'root', '', 'Test');
test_non_prep($con);
test_prep_sing($con);
test_prep_multi($con);
$con->close();
?>
Non prepared: 8548 Prepared not reused: 11356 Prepared reused: 8127
<?php
define('N',10000);
function delete($con) {
$con->query('DELETE FROM tt');
}
function test_non_prep($con) {
delete($con);
$t1 = microtime(true);
for($i = 0; $i < N; $i++) {
$s = 'Some text bla bla bla';
$stmt = $con->query("INSERT INTO tt VALUES ($i, '$s')");
}
$t2 = microtime(true);
$dt = (int)(($t2 - $t1) * 1000);
echo "Non prepared: $dt\r\n";
}
function test_prep_sing($con) {
delete($con);
$t1 = microtime(true);
for($i = 0; $i < N; $i++) {
$stmt = $con->prepare('INSERT INTO tt VALUES (:id, :name)');
$stmt->execute(array(':id' => $i, ':name' => 'Some text bla bla bla'));
}
$t2 = microtime(true);
$dt = (int)(($t2 - $t1) * 1000);
echo "Prepared not reused: $dt\r\n";
}
function test_prep_multi($con) {
delete($con);
$t1 = microtime(true);
$stmt = $con->prepare('INSERT INTO tt VALUES (:id, :name)');
for($i = 0; $i < N; $i++) {
$stmt->execute(array(':id' => $i, ':name' => 'Some text bla bla bla'));
}
$t2 = microtime(true);
$dt = (int)(($t2 - $t1) * 1000);
echo "Prepared reused: $dt\r\n";
}
$con = new PDO('mysql:host=localhost;dbname=Test','root', '');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
test_non_prep($con);
test_prep_sing($con);
test_prep_multi($con);
?>
Non prepared: 7472 Prepared not reused: 10545 Prepared reused: 9110
Not available at this time.
Let my emphasize: there is no guarantee that prepared statement / parameters will will improve performance, but sometimes it does.
Always use prepared statement / parameters when using SQL to access a database.
Parameters are for data values not for database names. You can not use parameters for table names or field names.
Parameters are for single data values not multiple data values. IN (?,?,?) and setting 3 values as 3 separate parameters will work, but IN (?) and setting 3 values as 1 parameter will not work.
Parameters can be used with LIKE and %. Just LIKE one parameter and add the necessary % to the value set.
The problems are not specific for SQL. Other database query languages has similar problems and parameters should also be used for those.
Examples:
But in practice developers always use parameters for those. When developers switch from SQL to ORM, then they know to use parameters.
Version | Date | Description |
---|---|---|
1.0 | August 5th 2016 | Initial version based on multiple old articles on Eksperten.dk |
1.1 | October 8th 2016 | Add content overview |
1.2 | August 12th 2021 | Add short section about non-SQL |
See list of all articles here
Please send comments to Arne Vajhøj