Parameterized 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): <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
Compile with:
gcc example.c -lsqlite3
Tested with gcc version 4.9.2 (Raspbian 4.9.2-10) and SQLite 3.8.7.1 <lang c>
- include <stdio.h>
- include <stdlib.h>
- include <sqlite3.h>
static const char* db_file = ":memory:"; // Create an in-memory database.
void check_error(int result_code, sqlite3 *db); int select_callback(void* data, int column_count, char** columns, char** column_names);
int main(void) {
sqlite3 *db; int result_code; char *sql; char *insert_statements[4]; sqlite3_stmt *compiled_statement; int i; // Open the database. result_code = sqlite3_open(db_file, &db); check_error(result_code, db); // Create the players table in the database. sql = "create table players(" "id integer primary key asc, " "name text, " "score real, " "active integer, " // Store the bool value as integer (see https://sqlite.org/datatype3.html chapter 2.1). "jerseyNum integer);"; result_code = sqlite3_exec(db, sql, NULL, NULL, NULL); check_error(result_code, db); // Insert some values into the players table. insert_statements[0] = "insert into players (name, score, active, jerseyNum) " "values ('Roethlisberger, Ben', 94.1, 1, 7);"; insert_statements[1] = "insert into players (name, score, active, jerseyNum) " "values ('Smith, Alex', 85.3, 1, 11);"; insert_statements[2] = "insert into players (name, score, active, jerseyNum) " "values ('Manning, Payton', 96.5, 0, 18);"; insert_statements[3] = "insert into players (name, score, active, jerseyNum) " "values ('Doe, John', 15, 0, 99);"; for (i=0; i<4; i++) { result_code = sqlite3_exec(db, insert_statements[i], NULL, NULL, NULL); check_error(result_code, db); } // Display the contents of the players table. printf("Before update:\n"); sql = "select * from players;"; result_code = sqlite3_exec(db, sql, select_callback, NULL, NULL); check_error(result_code, db); // Prepare the parametrized SQL statement to update player #99. sql = "update players set name=?, score=?, active=? where jerseyNum=?;"; result_code = sqlite3_prepare_v2(db, sql, -1, &compiled_statement, NULL); check_error(result_code, db); // Bind the values to the parameters (see https://sqlite.org/c3ref/bind_blob.html). result_code = sqlite3_bind_text(compiled_statement, 1, "Smith, Steve", -1, NULL); check_error(result_code, db); result_code = sqlite3_bind_double(compiled_statement, 2, 42); check_error(result_code, db); result_code = sqlite3_bind_int(compiled_statement, 3, 1); check_error(result_code, db); result_code = sqlite3_bind_int(compiled_statement, 4, 99); check_error(result_code, db); // Evaluate the prepared SQL statement. result_code = sqlite3_step(compiled_statement); if (result_code != SQLITE_DONE) { printf("Error #%d: %s\n", result_code, sqlite3_errmsg(db)); sqlite3_close(db); return result_code; } // Destroy the prepared statement object. result_code = sqlite3_finalize(compiled_statement); check_error(result_code, db);
// Display the contents of the players table. printf("After update:\n"); sql = "select * from players;"; result_code = sqlite3_exec(db, sql, select_callback, NULL, NULL); check_error(result_code, db); // Close the database connection. sqlite3_close(db); return EXIT_SUCCESS;
}
/*
Checks the result code from an SQLite operation. If it contains an error code then this function prints the error message, closes the database and exits.
- /
void check_error(int result_code, sqlite3 *db) {
if (result_code != SQLITE_OK) { printf("Error #%d: %s\n", result_code, sqlite3_errmsg(db)); sqlite3_close(db); exit(result_code); }
}
/* This callback function prints the results of the select statement. */ int select_callback(void* data, int column_count, char** columns, char** column_names) {
int i; for (i=0; i<column_count; i++) { printf(columns[i]); if (i < column_count-1) printf(" | "); } printf("\n");
}</lang>
- Output:
Before update: 1 | Roethlisberger, Ben | 94.1 | 1 | 7 2 | Smith, Alex | 85.3 | 1 | 11 3 | Manning, Payton | 96.5 | 0 | 18 4 | Doe, John | 15.0 | 0 | 99 After update: 1 | Roethlisberger, Ben | 94.1 | 1 | 7 2 | Smith, Alex | 85.3 | 1 | 11 3 | Manning, Payton | 96.5 | 0 | 18 4 | Smith, Steve | 42.0 | 1 | 99
C#
<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>
Clojure
<lang clojure> (require '[clojure.java.jdbc :as sql])
- Using h2database for this simple example.
(def db {:classname "org.h2.Driver"
:subprotocol "h2:file" :subname "db/my-dbname"})
(sql/update! db :players {:name "Smith, Steve" :score 42 :active true} ["jerseyNum = ?" 99])
- As an alternative to update!, use execute!
(sql/execute! db ["UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = ?" "Smith, Steve" 42 true 99]) </lang>
F#
<lang fsharp>open System.Data.SqlClient
[<EntryPoint>] let main argv =
use tConn = new SqlConnection("ConnectionString") use tCommand = new SqlCommand() tCommand.Connection <- tConn tCommand.CommandText <- "UPDATE players SET name = @name, score = @score, active = @active WHERE jerseyNum = @jerseyNum" tCommand.Parameters.Add(SqlParameter("@name", System.Data.SqlDbType.VarChar).Value = box "Smith, Steve") |> ignore tCommand.Parameters.Add(SqlParameter("@score", System.Data.SqlDbType.Int).Value = box 42) |> ignore tCommand.Parameters.Add(SqlParameter("@active", System.Data.SqlDbType.Bit).Value = box true) |> ignore tCommand.Parameters.Add(SqlParameter("@jerseyNum", System.Data.SqlDbType.Int).Value = box 99) |> ignore tCommand.ExecuteNonQuery() |> ignore 0</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
Haskell
Example uses the HDBC package:
<lang haskell> module Main (main) where
import Database.HDBC (IConnection, commit, run, toSql)
updatePlayers :: IConnection a => a -> String -> Int -> Bool -> Int -> IO Bool updatePlayers conn name score active jerseyNum = do
rowCount <- run conn "UPDATE players\ \ SET name = ?, score = ?, active = ?\ \ WHERE jerseyNum = ?" [ toSql name , toSql score , toSql active , toSql jerseyNum ] commit conn return $ rowCount == 1
main :: IO () main = undefined </lang>
You'll need an instance of a type with an instance for the IConnection type class in order to use this function, such as Connection from HDBC-postgresql.
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>
Julia
Uses the SQLite package. <lang julia>using SQLite
name = "Smith, Steve" jerseys = Dict("Smith, Steve" => 99) sqlbool(tf::Bool) = if(tf) "TRUE" else "FALSE" end
db = SQLite.DB() # no filename given, so create an in-memory temporary SQLite.execute!(db, "create table players (id integer primary key,
name text, score number, active bool, jerseynum integer)")
SQLite.query(db, "INSERT INTO players (name, score, active, jerseynum) values ('Jones, James', 9, 'FALSE', 99)") SQLite.query(db, "UPDATE players SET name = ?, score = ?, active = ? WHERE jerseynum = ?";
values = ["Smith, Steve", 42, sqlbool(true), jerseys[name]])
tbl = SQLite.query(db, "SELECT * from players") println(tbl)</lang>
- Output:
1×5 DataFrames.DataFrame │ Row │ id │ name │ score │ active │ jerseynum │ ├─────┼────┼────────────────┼───────┼────────┼───────────┤ │ 1 │ 1 │ "Smith, Steve" │ 42 │ "TRUE" │ 99 │
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>
Pascal
Tested with Free Pascal 2.6.4 (arm) and SQLite 3.8.7.1 <lang pascal>program Parametrized_SQL_Statement; uses
sqlite3, sysutils;
const
DB_FILE : PChar = ':memory:'; // Create an in-memory database.
var
DB :Psqlite3; ResultCode :Integer; SQL :PChar; InsertStatements :array [1..4] of PChar; CompiledStatement :Psqlite3_stmt; i :integer;
{ CheckError
Checks the result code from an SQLite operation. If it contains an error code then this procedure prints the error message, closes the database and halts the program. }
procedure CheckError(ResultCode: integer; DB: Psqlite3); begin
if ResultCode <> SQLITE_OK then begin writeln(format('Error #%d: %s', [ResultCode, sqlite3_errmsg(db)])); sqlite3_close(DB); halt(ResultCode); end;
end;
{ SelectCallback
This callback function prints the results of the select statement.}
function SelectCallback(Data: pointer; ColumnCount: longint; Columns: PPChar; ColumnNames: PPChar):longint; cdecl; var
i :longint; col :PPChar;
begin
col := Columns; for i:=0 to ColumnCount-1 do begin write(col^); // Print the current column value. inc(col); // Advance the pointer. if i<>ColumnCount-1 then write(' | '); end; writeln;
end;
begin
// Open the database. ResultCode := sqlite3_open(DB_FILE, @DB); CheckError(ResultCode, DB);
// Create the players table in the database. SQL := 'create table players(' + 'id integer primary key asc, ' + 'name text, ' + 'score real, ' + 'active integer, ' + // Store the bool value as integer (see https://sqlite.org/datatype3.html chapter 2.1). 'jerseyNum integer);'; ResultCode := sqlite3_exec(DB, SQL, nil, nil, nil); CheckError(ResultCode, DB);
// Insert some values into the players table. InsertStatements[1] := 'insert into players (name, score, active, jerseyNum) ' + 'values (Roethlisberger, Ben, 94.1, 1, 7);'; InsertStatements[2] := 'insert into players (name, score, active, jerseyNum) ' + 'values (Smith, Alex, 85.3, 1, 11);'; InsertStatements[3] := 'insert into players (name, score, active, jerseyNum) ' + 'values (Manning, Payton, 96.5, 0, 18);'; InsertStatements[4] := 'insert into players (name, score, active, jerseyNum) ' + 'values (Doe, John, 15, 0, 99);';
for i:=1 to 4 do begin ResultCode := sqlite3_exec(DB, InsertStatements[i], nil, nil, nil); CheckError(ResultCode, DB); end;
// Display the contents of the players table. writeln('Before update:'); SQL := 'select * from players;'; ResultCode := sqlite3_exec(DB, SQL, @SelectCallback, nil, nil); CheckError(ResultCode, DB);
// Prepare the parametrized SQL statement to update player #99. SQL := 'update players set name=?, score=?, active=? where jerseyNum=?;'; ResultCode := sqlite3_prepare_v2(DB, SQL, -1, @CompiledStatement, nil); CheckError(ResultCode, DB);
// Bind the values to the parameters (see https://sqlite.org/c3ref/bind_blob.html). ResultCode := sqlite3_bind_text(CompiledStatement, 1, PChar('Smith, Steve'), -1, nil); CheckError(ResultCode, DB); ResultCode := sqlite3_bind_double(CompiledStatement, 2, 42); CheckError(ResultCode, DB); ResultCode := sqlite3_bind_int(CompiledStatement, 3, 1); CheckError(ResultCode, DB); ResultCode := sqlite3_bind_int(CompiledStatement, 4, 99); CheckError(ResultCode, DB);
// Evaluate the prepared SQL statement. ResultCode := sqlite3_step(CompiledStatement); if ResultCode <> SQLITE_DONE then begin writeln(format('Error #%d: %s', [ResultCode, sqlite3_errmsg(db)])); sqlite3_close(DB); halt(ResultCode); end;
// Destroy the prepared statement object. ResultCode := sqlite3_finalize(CompiledStatement); CheckError(ResultCode, DB);
// Display the contents of the players table. writeln('After update:'); SQL := 'select * from players;'; ResultCode := sqlite3_exec(DB, SQL, @SelectCallback, nil, nil); CheckError(ResultCode, DB);
// Close the database connection. sqlite3_close(db);
end.</lang>
- Output:
Before update: 1 | Roethlisberger, Ben | 94.1 | 1 | 7 2 | Smith, Alex | 85.3 | 1 | 11 3 | Manning, Payton | 96.5 | 0 | 18 4 | Doe, John | 15.0 | 0 | 99 After update: 1 | Roethlisberger, Ben | 94.1 | 1 | 7 2 | Smith, Alex | 85.3 | 1 | 11 3 | Manning, Payton | 96.5 | 0 | 18 4 | Smith, Steve | 42.0 | 1 | 99
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>
PureBasic
<lang PureBasic>UseSQLiteDatabase()
Procedure CheckDatabaseUpdate(database, query$)
result = DatabaseUpdate(database, query$) If result = 0 PrintN(DatabaseError()) EndIf ProcedureReturn result
EndProcedure
If OpenConsole()
If OpenDatabase(0, ":memory:", "", "") ;create players table with sample data CheckDatabaseUpdate(0, "CREATE table players (name, score, active, jerseyNum)") CheckDatabaseUpdate(0, "INSERT INTO players VALUES ('Jones, Bob',0,'N',99)") CheckDatabaseUpdate(0, "INSERT INTO players VALUES ('Jesten, Jim',0,'N',100)") CheckDatabaseUpdate(0, "INSERT INTO players VALUES ('Jello, Frank',0,'N',101)")
Define name$, score, active$, jerseynum name$ = "Smith, Steve" score = 42 active$ ="TRUE" jerseynum = 99 SetDatabaseString(0, 0, name$) SetDatabaseLong(0, 1, score) SetDatabaseString(0, 2, active$) SetDatabaseLong(0, 3, jerseynum) CheckDatabaseUpdate(0, "UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = ?") ;display database contents If DatabaseQuery(0, "Select * from players") While NextDatabaseRow(0) name$ = GetDatabaseString(0, 0) score = GetDatabaseLong(0, 1) active$ = GetDatabaseString(0, 2) jerseynum = GetDatabaseLong(0, 3) row$ = "['" + name$ + "', " + score + ", '" + active$ + "', " + jerseynum + "]" PrintN(row$) Wend FinishDatabaseQuery(0) EndIf CloseDatabase(0) Else PrintN("Can't open database !") EndIf Print(#CRLF$ + #CRLF$ + "Press ENTER to exit"): Input() CloseConsole()
EndIf</lang>
Sample output:
['Smith, Steve', 42, 'TRUE', 99] ['Jesten, Jim', 0, 'N', 100] ['Jello, Frank', 0, 'N', 101]
Python
<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>
- 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
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
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
SQL
<lang sql> -- This works in Oracle's SQL*Plus command line utility
VARIABLE P_NAME VARCHAR2(20); VARIABLE P_SCORE NUMBER; VARIABLE P_ACTIVE VARCHAR2(5); VARIABLE P_JERSEYNUM NUMBER;
begin
- P_NAME := 'Smith, Steve';
- P_SCORE := 42;
- P_ACTIVE := 'TRUE';
- P_JERSEYNUM := 99;
end; /
drop table players;
create table players ( NAME VARCHAR2(20), SCORE NUMBER, ACTIVE VARCHAR2(5), JERSEYNUM NUMBER );
insert into players values ('No name',0,'FALSE',99);
commit;
select * from players;
UPDATE players
SET name = :P_NAME, score = :P_SCORE, active = :P_ACTIVE WHERE jerseyNum = :P_JERSEYNUM;
commit;
select * from players; </lang>
SQL> SQL> NAME SCORE ACTIV JERSEYNUM -------------------- ---------- ----- ---------- No name 0 FALSE 99 SQL> SQL> 2 3 1 row updated. SQL> SQL> Commit complete. SQL> SQL> NAME SCORE ACTIV JERSEYNUM -------------------- ---------- ----- ---------- Smith, Steve 42 TRUE 99
Tcl
<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>