Parameterized SQL statement

Parameterized SQL statement
You are encouraged to solve this task according to the task description, using any language you may know.

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

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>