Parameterized SQL statement

Parameterized SQL statements are an easy way to avoid SQL injection attacks. SQL drivers and libraries will automatically "sanitize" input to parameterized SQL statements to avoid these catastrophic database attacks. Second, parameterized SQL performs better. A lot better.

Using a SQL update statement like this one (spacing is optional): <lang sql>UPDATE players

  SET name = 'Smith, Steve', score = 42, active = true
  WHERE jerseyNum = 99</lang>show how to make a parameterized SQL statement, set the parameters to the values given above, and execute the statement.

Non-parameterized SQL is the GoTo statement of database programming. Don't do it, and make sure your coworkers don't either.

8th

<lang forth> \ assuming the var 'db' contains an opened database with a schema matching the problem: db @ "UPDATE players SET name=?1,score=?2,active=?3 WHERE jerseyNum=?4" db:prepare var, stmt

\ bind values to the statement: stmt @ 1 "Smith, Steve" db:bind

      2 42 db:bind
      3 true db:bind
      4 99 db:bind

\ execute the query db @ swap db:exec

</lang>

Ada

<lang Ada> -- Version for sqlite with GNATCOLL.SQL_Impl; use GNATCOLL.SQL_Impl; with GNATCOLL.SQL.Exec; use GNATCOLL.SQL.Exec; with GNATCOLL.SQL.Sqlite; use GNATCOLL.SQL;

procedure Prepared_Query is

  DB_Descr : Database_Description;
  Conn     : Database_Connection;
  Query    : Prepared_Statement;
  --sqlite does not support boolean fields
  True_Str : aliased String          := "TRUE";
  Param    : SQL_Parameters (1 .. 4) :=
    (1 => (Parameter_Text, null),
     2 => (Parameter_Integer, 0),
     3 => (Parameter_Text, null),
     4 => (Parameter_Integer, 0));

begin

  -- Allocate and initialize the description of the connection
  Setup_Database (DB_Descr, "rosetta.db", "", "", "", DBMS_Sqlite);
  -- Allocate the connection
  Conn := Sqlite.Build_Sqlite_Connection (DB_Descr);
  -- Initialize the connection
  Reset_Connection (DB_Descr, Conn);
  Query :=
     Prepare
       ("UPDATE players SET name = ?, score = ?, active = ? " &
        " WHERE jerseyNum = ?");
  declare
     Name : aliased String := "Smith, Steve";
  begin
     Param := ("+" (Name'Access), "+" (42), "+" (True_Str'Access), "+" (99));
     Execute (Conn, Query, Param);
  end;
  Commit_Or_Rollback (Conn);
  Free (Conn);
  Free (DB_Descr);

end Prepared_Query;

</lang>

C_sharp

<lang csharp> using System.Data.Sql; using System.Data.SqlClient;

namespace ConsoleApplication1 {

   class Program
   {
       static void Main(string[] args)
       {
           SqlConnection tConn = new SqlConnection("ConnectionString");

           SqlCommand tCommand = new SqlCommand();
           tCommand.Connection = tConn;
           tCommand.CommandText = "UPDATE players SET name = @name, score = @score, active = @active WHERE jerseyNum = @jerseyNum";

           tCommand.Parameters.Add(new SqlParameter("@name", System.Data.SqlDbType.VarChar).Value = "Smith, Steve");
           tCommand.Parameters.Add(new SqlParameter("@score", System.Data.SqlDbType.Int).Value = "42");
           tCommand.Parameters.Add(new SqlParameter("@active", System.Data.SqlDbType.Bit).Value = true);
           tCommand.Parameters.Add(new SqlParameter("@jerseyNum", System.Data.SqlDbType.Int).Value = "99");

           tCommand.ExecuteNonQuery();
       }
   }

} </lang>

Go

<lang go>package main

import (

   "database/sql"
   "fmt"

   _ "github.com/mattn/go-sqlite3"

)

func main() {

   db, _ := sql.Open("sqlite3", "rc.db")
   defer db.Close()
   db.Exec(`create table players (name, score, active, jerseyNum)`)
   db.Exec(`insert into players values ("",0,0,"99")`)
   db.Exec(`insert into players values ("",0,0,"100")`)

   // Parameterized
   db.Exec(`update players set name=?, score=?, active=? where jerseyNum=?`,
       "Smith, Steve", 42, true, "99")

   rows, _ := db.Query("select * from players")
   var (
       name      string
       score     int
       active    bool
       jerseyNum string
   )
   for rows.Next() {
       rows.Scan(&name, &score, &active, &jerseyNum)
       fmt.Printf("%3s %12s %3d %t\n", jerseyNum, name, score, active)
   }
   rows.Close()

}</lang>

Output:

 99 Smith, Steve  42 true
100                0 false

Java

<lang java>import java.sql.DriverManager; import java.sql.Connection; import java.sql.PreparedStatement;

public class DBDemo{

  private String protocol; //set this to some connection protocol like "jdbc:sqlserver://"
  private String dbName;   //set this to the name of your database
  private String username;
  private String password;

  Connection conn = DriverManager.getConnection(protocol + dbName, username, password);
  PreparedStatement query;

  public int setUpAndExecPS(){
     query = conn.prepareStatement(
           "UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = ?");

     query.setString(1, "Smith, Steve");//automatically sanitizes and adds quotes
     query.setInt(2, 42);
     query.setBoolean(3, true);
     query.setInt(4, 99);
     //there are similar methods for other SQL types in PerparedStatement

     return query.executeUpdate();//returns the number of rows changed
     //PreparedStatement.executeQuery() will return a java.sql.ResultSet,
     //execute() will simply return a boolean saying whether it succeeded or not
  }

}</lang>

Mathematica

<lang Mathematica>Needs["DatabaseLink`"]; conn=OpenSQLConnection[JDBC["ODBC(DSN)", "testdb"], "Username" -> "John", "Password" -> "JohnsPassword"]; SQLUpdate[conn,"players",{"name","score","active"},{"Smith, Steve", 42,"TRUE"},SQLColumn["jerseyNum"] = 99]; CloseSQLConnection[conn];</lang>

NetRexx

Using an Apache Derby embedded database: <lang NetRexx>/* NetRexx */ options replace format comments java crossref symbols nobinary

import java.sql.

-- ============================================================================= class RParameterizedSQLSimple public

 properties indirect
   connexion = Connection

 properties inheritable constant
   DRIVER = "org.apache.derby.jdbc.EmbeddedDriver"
   DBURL = "jdbc:derby:"
   DBNAME = "/workspace/DB.DerbySamples/DB/TEAMS01"
   DBMODE_CREATE   = ";create=true"
   DBMODE_NOCREATE = ";create=false"
   DBMODE_SHUTDOWN = ";shutdown=true"

-- ============================================================================= method RParameterizedSQLSimple()

 setConnexion(null)
 return

-- ============================================================================= method createConnexion() inheritable returns Connection signals ClassNotFoundException, InstantiationException, IllegalAccessException

 if getConnexion() = null then do
   props = Properties()
   props.put("user", "user1")
   props.put("password", "user1")

   xURL = String DBURL || DBNAME || DBMODE_CREATE
   loadDriver(DRIVER)
   setConnexion(DriverManager.getConnection(xURL, props))
   end

 return getConnexion()

-- ============================================================================= method shutdownConnexion() inheritable returns boolean signals SQLException

 dbState = boolean
 xURL = String DBURL || DBNAME || DBMODE_SHUTDOWN

 do
   DriverManager.getConnection(xURL)
   dbState = isTrue

 catch se = SQLException
   if (se.getErrorCode() = 50000) & ("XJ015".equals(se.getSQLState())) then do
     say "Derby shut down normally"
     dbState = isTrue
     end
   else if (se.getErrorCode() = 45000) & ("08006".equals(se.getSQLState())) then do
     say "Derby database shut down normally"
     dbState = isTrue
     end
   else do
     say "Derby did not shut down normally"
     dbState = isFalse
     signal se
     end
 end

 return dbState

-- ============================================================================= method loadDriver(xdriver = String) inheritable static signals ClassNotFoundException, InstantiationException, IllegalAccessException

 do
   Class.forName(xdriver).newInstance()
   say "Loaded the appropriate driver"

 catch cnfe = ClassNotFoundException
   say "Unable to load the JDBC driver" xdriver
   say "Please check your CLASSPATH."
   signal cnfe

 catch ie = InstantiationException
   say "Unable to instantiate the JDBC driver" xdriver
   signal ie

 catch iae = IllegalAccessException
   say "Not allowed to access the JDBC driver" xdriver
   signal iae

end

 return

-- ============================================================================= method updatePlayer(jerseyNum = int, name = String, score = int, active = boolean) binary inheritable returns int signals SQLException

 updateSQL = "" -
   || "UPDATE TEAM.PLAYERS" -
   || "  SET NAME = ?, SCORE = ?, ACTIVE = ?" -
   || "  WHERE JERSEYNUM = ?"

 rowCt = int
 ix = int 0

 ps = getConnexion().prepareStatement(updateSQL)
 ix = ix + 1; ps.setString(ix, name)
 ix = ix + 1; ps.setInt(ix, score)
 ix = ix + 1; ps.setBoolean(ix, active)
 ix = ix + 1; ps.setInt(ix, jerseyNum)
 rowCt = ps.executeUpdate()

 return rowCt

-- ============================================================================= method main(args = String[]) public static

 do
   tda = RParameterizedSQLSimple()
   tda.createConnexion()
 
   if tda.getConnexion() \= null then do
       updated = tda.updatePlayer(99, "Smith, Steve", 42, isTrue)
       if updated > 0 then say "Update successful"
       else say "Update failed"
 
     finally
       tda.shutdownConnexion()
     end

 catch ex = Exception
   ex.printStackTrace
 end

 return

-- ============================================================================= method isTrue() public static returns boolean

 return 1 == 1

-- ============================================================================= method isFalse() public static returns boolean

 return \isTrue

</lang>

Objeck

<lang objeck>use IO; use ODBC;

bundle Default {

  class Sql {
     function : Main(args : String[]) ~ Nil {
     conn := Connection->New("ds", "user", "password");
     if(conn <> Nil) {
        sql := "UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = ?";
        pstmt := conn->CreateParameterStatement(sql);
        pstmt->SetVarchar(1, "Smith, Steve");
        pstmt->SetInt(2, 42);
        pstmt->SetBit(3, true);
        pstmt->SetInt(4, 99);
        pstmt->Update()->PrintLine();
        conn->Close();
     };
  }

}</lang>

Perl

<lang perl>use DBI;

my $db = DBI->connect('DBI:mysql:mydatabase:host','login','password');

$statment = $db->prepare("UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = ?");

$rows_affected = $statment->execute("Smith, Steve",42,'true',99);</lang>

Perl 6

<lang perl6>use DBIish;

my $db = DBIish.connect('DBI:mysql:mydatabase:host','login','password');

my $update = $db.prepare("UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = ?");

my $rows-affected = $update.execute("Smith, Steve",42,'true',99);</lang>

PHP

<lang php>$updatePlayers = "UPDATE `players` SET `name` = ?, `score` = ?, `active` = ?\n". "WHERE `jerseyNum` = ?"; $dbh = new PDO( "mysql:dbname=db;host=localhost", "username", "password" );

$updateStatement = $dbh->prepare( $updatePlayers );

$updateStatement->bindValue( 1, "Smith, Steve", PDO::PARAM_STR ); $updateStatement->bindValue( 2, 42, PDO::PARAM_INT ); $updateStatement->bindValue( 3, 1, PDO::PARAM_INT ); $updateStatement->bindValue( 4, 99, PDO::PARAM_INT );

$updateStatement->execute();

// alternatively pass parameters as an array to the execute method $updateStatement = $dbh->prepare( $updatePlayers ); $updateStatement->execute( array( "Smith, Steve", 42, 1, 99 ) );</lang>

PicoLisp

As PicoLisp uses normal function calls for DB manipulations, parameters are always treated as plain data and are not executed. <lang PicoLisp>(for P (collect 'jerseyNum '+Players 99)

  (put!> P 'name "Smith, Steve")
  (put!> P 'score 42)
  (put!> P 'active T) )</lang>

Python

Translation of: Ruby

<lang python>import sqlite3

db = sqlite3.connect(':memory:')

setup

db.execute('create temp table players (name, score, active, jerseyNum)') db.execute('insert into players values ("name",0,"false",99)') db.execute('insert into players values ("name",0,"false",100)')

demonstrate parameterized SQL

example 1 -- simple placeholders

db.execute('update players set name=?, score=?, active=? where jerseyNum=?', ('Smith, Steve', 42, True, 99))

example 2 -- named placeholders

db.execute('update players set name=:name, score=:score, active=:active where jerseyNum=:num',

   {'num': 100,
    'name': 'John Doe',
    'active': False,
    'score': -1}

)

and show the results

for row in db.execute('select * from players'):

  print(row)</lang>

outputs

(u'Smith, Steve', 42, 1, 99)
(u'John Doe', -1, 0, 100)

Racket

lang racket

(require db)

(define con (postgresql-connect #:user user #:database db #:password password)) (define pst (prepare pgc "UPDATE players

                           SET name = ?, score = ?, active = ? 
                           WHERE jerseyNum = ?"))

(define bst (bind-prepared-statement pst '("Smith, Steve" 42 #t 99))) (query-value con bst) </lang>

Ruby

Using the

Library: sqlite3-ruby

gem

<lang ruby>require 'sqlite3'

db = SQLite3::Database.new(":memory:")

setup

db.execute('create temp table players (name, score, active, jerseyNum)') db.execute('insert into players values ("name",0,"false",99)') db.execute('insert into players values ("name",0,"false",100)') db.execute('insert into players values ("name",0,"false",101)')

demonstrate parameterized SQL

example 1 -- simple placeholders

db.execute('update players set name=?, score=?, active=? where jerseyNum=?', 'Smith, Steve', 42, true, 99)

example 2 -- named placeholders

db.execute('update players set name=:name, score=:score, active=:active where jerseyNum=:num',

   :num => 100,
   :name => 'John Doe',
   :active => false,
   :score => -1

)

example 3 -- numbered placeholders

stmt = db.prepare('update players set name=?4, score=?3, active=?2 where jerseyNum=?1') stmt.bind_param(1, 101) stmt.bind_param(2, true) stmt.bind_param(3, 3) stmt.bind_param(4, "Robert'; DROP TABLE players--") stmt.execute

and show the results

db.execute2('select * from players') {|row| p row}</lang> outputs

["name", "score", "active", "jerseyNum"]
["Smith, Steve", "42", "true", "99"]
["John Doe", "-1", "false", "100"]
["Robert'; DROP TABLE players--", "3", "true", "101"]

Run BASIC

<lang runbasic>sqliteconnect #mem, ":memory:"

mem execute("CREATE table players (name, score, active, jerseyNum)")
mem execute("INSERT INTO players VALUES ('Jones, Bob',0,'N',99)")
mem execute("INSERT INTO players VALUES ('Jesten, Jim',0,'N',100)")
mem execute("INSERT INTO players VALUES ('Jello, Frank',0,'N',101)")

sql$ = " UPDATE players

  SET name = 'Smith, Steve', 
  score   = 42, 
  active  = 'TRUE'
  WHERE jerseyNum = 99"

mem execute(sql$)
mem execute("SELECT * FROM players ORDER BY jerseyNum")

WHILE #mem hasanswer() #row = #mem #nextrow() name$ = #row name$() score = #row score() active$ = #row active$() jerseyNum = #row jerseyNum()

print name$;chr$(9);score;chr$(9);active$;chr$(9);jerseyNum WEND end</lang>

Output
Smith, Steve	42	TRUE	99
Jesten, Jim	0	N	100
Jello, Frank	0	N	101

PureBasic

<lang PureBasic>UseSQLiteDatabase()

DatabaseFile$ = GetTemporaryDirectory()+"/Batadase.sqt"

all kind of variables for the given case

table$ = "players"
name$ = "Smith, Steve"  
score.w = 42
active$ ="TRUE"
jerseynum.w =99

If OpenDatabase(0, DatabaseFile$, "", "")
   Result =  DatabaseUpdate((0, "UPDATE "+table$+" SET name = '"+name$+"', score = '"+Str(score)+"', active = '"+active$+"' WHERE jerseyNum = "+Str(num)+";")
    If Result = 0
       Debug DatabaseError()
    EndIf
   CloseDatabase(0)
Else
   Debug "Can't open database !"
EndIf</lang>

Seed7

The library sql_base.s7i provides access to databases. The type database describes a database connection and the type sqlStatement can store a prepared statement. In the example below the table players is created and filled with hard coded SQL statements, that are executed without parametrization. The SQL statement to update the table uses parametrization. The SQL statement is prepared, parameters are bound and the statement is executed. Finally a SQL select statement is prepared, executed and the result rows are fetched. A column from a result row is retrieved with the function column.

<lang seed7>$ include "seed7_05.s7i";

 include "sql_base.s7i";

const proc: main is func

 local
   var database: testDb is database.value;
   var sqlStatement: statement is sqlStatement.value;
   var string: name is "Smith, Steve";
 begin
   testDb := openDatabase(DB_SQLITE, "test", "test", "test");
   execute(testDb, "create table players (name CHAR(32), score INTEGER, active CHAR, jerseyNum INTEGER)");
   execute(testDb, "insert into players values ('Jones, Bob',0,0,99)");
   execute(testDb, "insert into players values ('Jesten, Jim',0,0,100)");
   execute(testDb, "insert into players values ('Jello, Frank',0,0,101)");
   statement := prepare(testDb, "update players set name = ?, score = ?, active = ? \
                                \where jerseyNum = ?");
   bind(statement, 1, name);
   bind(statement, 2, 42);
   bind(statement, 3, TRUE);
   bind(statement, 4, 99);
   execute(statement);
   statement := prepare(testDb, "select * from players");
   execute(statement);
   while fetch(statement) do
     writeln(column(statement, 1, string) <& " " <&
             column(statement, 2, integer) <& " " <&
             column(statement, 3, boolean) <& " " <&
             column(statement, 4, integer));
   end while;
   execute(testDb, "drop table players");
   close(testDb);
 end func;</lang>

Output:

Smith, Steve 42 TRUE 99
Jesten, Jim 0 FALSE 100
Jello, Frank 0 FALSE 101

Tcl

Works with: Tcl version 8.6

<lang tcl>package require Tcl 8.6

These next two lines are the only ones specific to SQLite

package require tdbc::sqlite3 set db [tdbc::sqlite3::connection new /path/to/database.sql]

Use a helper procedure to make a scope

proc setPlayer {db jersey -> playerName playerScore playerActive} {

   # Note that the '->' above is just syntactic noise for readability
   $db allrows {

UPDATE players SET name = :playerName, score = :playerScore, active = :playerActive WHERE jerseyNum = :jersey

   }
   # The named parameters are bound to local variables by default

}

How to use...

setPlayer $db 99 -> "Smith, Steve" 42 true

With apologies to http://xkcd.com/327/

setPlayer $db 76 -> "Robert'; DROP TABLE players--" 0 false $db close</lang>