Parameterized SQL statement: Difference between revisions
({{omit from|PARI/GP}}) |
(Creation of the Ada entry) |
||
Line 6: | Line 6: | ||
WHERE jerseyNum = 99</lang> |
WHERE jerseyNum = 99</lang> |
||
show how to make a parameterized SQL statement, set the parameters to the values given above, and execute the statement. |
show how to make a parameterized SQL statement, set the parameters to the values given above, and execute the statement. |
||
=={{header|Ada}}== |
|||
<lang Ada> |
|||
-- Version for sqlite |
|||
with Ada.Exceptions; use Ada.Exceptions; |
|||
with Ada.Text_IO; use Ada.Text_IO; |
|||
with GNATCOLL.SQL_Impl; use GNATCOLL, GNATCOLL.SQL_Impl; |
|||
with GNATCOLL.SQL.Exec; use GNATCOLL.SQL, GNATCOLL.SQL.Exec; |
|||
with GNATCOLL.SQL.Sqlite; use GNATCOLL.SQL.Sqlite; |
|||
procedure Prepared_Query is |
|||
DB_Descr : Database_Description; |
|||
Connection : Database_Connection; |
|||
Prep_update : Prepared_Statement; |
|||
--sqlite does not support boolean fields |
|||
True_Str : aliased String := "TRUE"; |
|||
My_Parameters : Exec.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 |
|||
Connection := Build_Sqlite_Connection (DB_Descr); |
|||
-- Initialize the connection |
|||
Reset_Connection (DB_Descr, Connection); |
|||
Prep_update := |
|||
Prepare |
|||
("UPDATE players SET name = ?, score = ?, active = ? " & |
|||
" WHERE jerseyNum = ?", |
|||
Use_Cache => False, |
|||
On_Server => True, |
|||
Name => "main_proc"); |
|||
declare |
|||
Name : aliased String := "Smith, Steve"; |
|||
begin |
|||
My_Parameters := |
|||
("+" (Name'Access), |
|||
"+" (42), |
|||
"+" (True_Str'Access), |
|||
"+" (99)); |
|||
Execute (Connection, Prep_update, My_Parameters); |
|||
end; |
|||
Execute (Connection, "COMMIT"); |
|||
Close (Connection); |
|||
Free (DB_Descr); |
|||
exception |
|||
when E : others => |
|||
Put_Line (Standard_Error, Exception_Information (E)); |
|||
end Prepared_Query; |
|||
</lang> |
|||
=={{header|C_sharp|C#}}== |
=={{header|C_sharp|C#}}== |
Revision as of 07:55, 16 February 2011
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.
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.
Ada
<lang Ada> -- Version for sqlite
with Ada.Exceptions; use Ada.Exceptions; with Ada.Text_IO; use Ada.Text_IO; with GNATCOLL.SQL_Impl; use GNATCOLL, GNATCOLL.SQL_Impl; with GNATCOLL.SQL.Exec; use GNATCOLL.SQL, GNATCOLL.SQL.Exec; with GNATCOLL.SQL.Sqlite; use GNATCOLL.SQL.Sqlite;
procedure Prepared_Query is
DB_Descr : Database_Description; Connection : Database_Connection; Prep_update : Prepared_Statement; --sqlite does not support boolean fields True_Str : aliased String := "TRUE"; My_Parameters : Exec.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 Connection := Build_Sqlite_Connection (DB_Descr); -- Initialize the connection Reset_Connection (DB_Descr, Connection); Prep_update := Prepare ("UPDATE players SET name = ?, score = ?, active = ? " & " WHERE jerseyNum = ?", Use_Cache => False, On_Server => True, Name => "main_proc");
declare Name : aliased String := "Smith, Steve"; begin My_Parameters := ("+" (Name'Access), "+" (42), "+" (True_Str'Access), "+" (99)); Execute (Connection, Prep_update, My_Parameters); end; Execute (Connection, "COMMIT"); Close (Connection); Free (DB_Descr);
exception
when E : others => Put_Line (Standard_Error, Exception_Information (E));
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>
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>
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>
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>
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)
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"]
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>
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>