Prepared statement / Parameters

Content:

  1. The problems
    1. Unfortunate (non-malicious) single quotes
    2. Malicious single quotes aka SQL injection
    3. Date formats
  2. The wrong solution
  3. The right solution
    1. Concept
    2. Examples
    3. Performance
  4. Conclusion
  5. Limitations
  6. Non-SQL

The problems

Most people that have written programs using a database has encountered one or more of the problems.

Unfortunate (non-malicious) single quotes:

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')"

Malicious single quotes aka SQL injection:

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:

Date formats frequently gives problem with database.

What format to use?

and:

and what controls the format?

The wrong solution

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:

Concept:

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.

Examples:

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.

Performance

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.

Conclusion

Always use prepared statement / parameters when using SQL to access a database.

Limitations:

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.

Non-SQL:

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.

Article history:

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

Other articles:

See list of all articles here

Comments:

Please send comments to Arne Vajhøj