Parameterized SQL statement: Difference between revisions
No edit summary |
(Add Seed7 example) |
||
Line 496: | Line 496: | ||
Debug "Can't open database !" |
Debug "Can't open database !" |
||
EndIf</lang> |
EndIf</lang> |
||
=={{header|Seed7}}== |
|||
The library [http://seed7.sourceforge.net/libraries/sql_base.htm sql_base.s7i] provides access to databases. |
|||
The type [http://seed7.sourceforge.net/libraries/sql_base.htm#database database] describes a database connection |
|||
and the type [http://seed7.sourceforge.net/libraries/sql_base.htm#sqlStatement sqlStatement] can store a prepared statement. |
|||
In the example below the table ''players'' is created and filled with hard coded SQL statements, that are ''execute''d without parametrization. |
|||
The SQL statement to update the table uses parametrization. |
|||
The SQL statement is [http://seed7.sourceforge.net/libraries/sql_base.htm#prepare%28in_database,in_string%29 prepared], |
|||
parameters are [http://seed7.sourceforge.net/libraries/sql_base.htm#bind%28inout_sqlStatement,in_integer,in_integer%29 bound] and |
|||
the statement is [http://seed7.sourceforge.net/libraries/sql_base.htm#execute%28inout_sqlStatement%29 executed]. |
|||
Finally a SQL select statement is prepared, executed and the result rows are [http://seed7.sourceforge.net/libraries/sql_base.htm#fetch%28in_sqlStatement%29 fetched]. |
|||
A column from a result row is retrieved with the function [http://seed7.sourceforge.net/libraries/sql_base.htm#column%28in_sqlStatement,in_integer,attr_integer%29 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> |
|||
{{out}} |
|||
<pre> |
|||
Smith, Steve 42 TRUE 99 |
|||
Jesten, Jim 0 FALSE 100 |
|||
Jello, Frank 0 FALSE 101 |
|||
</pre> |
|||
=={{header|Tcl}}== |
=={{header|Tcl}}== |
Revision as of 17:14, 6 October 2014
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.
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
<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
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
<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>