Parameterized SQL statement: Difference between revisions
(→{{header|Tcl}}: illustrate that we're resistant to attack...) |
m (Added note about SQL injection) |
||
Line 1: | Line 1: | ||
{{task|Database operations}}Parameterized SQL statements are an easy way to avoid [[wp:SQL injection]] attacks. SQL drivers and libraries will automatically "sanitize" input to parameterized SQL statements to avoid these catastrophic database attacks. |
|||
⚫ | |||
⚫ | |||
UPDATE players SET name = 'Smith, Steve', score = 42, active = true WHERE jerseyNum = 99 |
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. |
show how to make a parameterized SQL statement, set the parameters to the values given above, and execute the statement. |
Revision as of 15:11, 8 October 2009
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 wp: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
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.
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>
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>