Web Development Summary 9: SQL prepared statments

SQL prepared statement help make accessing your sql database more secure by seperating the statement from the users input. This prevents SQL injection attack, where users entering statements in to input field which would allow them to get important information out of you database. By having the statment preprocessed and just waiting for the need variables it will only accept the correct input. Prepaired statements also help improve performance. The statment is only processed once but can be executed many times. They are also transmit in a bianary format which does not need to be converted in to a string to be sent. In order to use prepared statements you need the mysql connector .jar file.

(Create string of connection url within specified format with machine name, port number and database name.)
String connectionURL = "jdbc:mysql://port/databasename";
(declare a connection by using Connection interface)
Connection connection = null;
(declare object of Statement interface that uses for executing sql statements.)
PreparedStatement pstatement = null;
(Load JBBC driver "com.mysql.jdbc.Driver")
Class.forName("com.mysql.jdbc.Driver").newInstance();
try {
(Create a connection by using getConnection() method that takes parameters of string type connection url, user name and password to connect to database.) connection = DriverManager.getConnection
(connectionURL, "databaseusername", "password");
(sql query to insert values in the secified table.)
String queryString = "INSERT INTO table(columnnames) VALUES (?, ?, ?)";
(createStatement() is used for create statement object that is used for sending sql statements to the specified database.0
pstatement = connection.prepareStatement(queryString);
pstatement.setString(1, column1);
pstatement.setString(2, column2);
pstatement.setString(3, column3);
updateQuery = pstatement.executeUpdate();
}
catch (Exception ex) {
out.println("Unable to connect to batabase.");
}
finally {
(close all the connections.)
pstatement.close();
connection.close();
}
} (see http://www.roseindia.net/jsp/prepared-statement-query.shtml for a more detailed example)

Prepaired statements only work with INSERT, REPLACE, UPDATE, DELETE, CREATE TABLE, and SELECT queries. When you are accepting input from the user to a sql statment it is alway a good idea to use a prepaired statement.