Parameterized SQL statement

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.

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.

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

Works with: Tcl version 8.6

<lang tcl>package require Tcl 8.6

1. 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]

1. 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

}

1. How to use...

setPlayer $db 99 -> "Smith, Steve" 42 true

1. With apologies to http://xkcd.com/327/

setPlayer $db 76 -> "Robert'; DROP TABLE players--" 0 false $db close</lang>