Parametrized SQL statement

From Rosetta Code
Jump to: navigation, search
Task
Parametrized SQL statement
You are encouraged to solve this task according to the task description, using any language you may know.
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):

UPDATE players
SET name = 'Smith, Steve', score = 42, active = TRUE
WHERE jerseyNum = 99
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.

Contents

[edit] 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;
 
 

[edit] C#

 
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();
}
}
}
 

[edit] 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()
}
Output:
 99 Smith, Steve  42 true
100                0 false

[edit] 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
}
}

[edit] 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];

[edit] NetRexx

Using an Apache Derby embedded database:

/* 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
 

[edit] 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();
};
}
}

[edit] 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);

[edit] Perl 6

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);

[edit] 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 ) );

[edit] PicoLisp

As PicoLisp uses normal function calls for DB manipulations, parameters are always treated as plain data and are not executed.

(for P (collect 'jerseyNum '+Players 99)
(put!> P 'name "Smith, Steve")
(put!> P 'score 42)
(put!> P 'active T) )

[edit] Python

Translation of: Ruby
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)

outputs

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

[edit] 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)
 

[edit] Ruby

Using the
Library: sqlite3-ruby
gem
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}

outputs

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

[edit] Run BASIC

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
Output
Smith, Steve	42	TRUE	99
Jesten, Jim	0	N	100
Jello, Frank	0	N	101

[edit] 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

[edit] Tcl

Works with: Tcl version 8.6
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
Personal tools
Namespaces

Variants
Actions
Community
Explore
Misc
Toolbox