CST363 - Week 4

Using Java with SQL

This week my team implemented methods for a Java application that interacts with a web server and a database. A big part of that process is how to deal with SQL select statements. After a connection has been made to the database, a prepared statement is created as a string and sent as a query to the database. The results are read into a Java ResultSet object which is then iterated through using a while loop. While the result set still contains rows to read, we loop through each row and extract the data needed. A simple way to store the extracted data is in an array list. When we are finished reading and processing our results, we close the database connection. 

SQL Injection Attacks and Parameterized Statements

SQL injection attacks occur when a malicious actor sends potentially damaging commands to an SQL database without authorization. An example of this exploit occurs when a website allows users to send commands to a database that are not checked for safety first. Certain characters, such as ", are treated a special way by databases and must be accompanied by an escape character, \, in order to ensure that an error does not occur. If they are not, a command such as "drop database" can be deliberately added to a malformed string and the database will interpret it as a normal command. 

Parameterized statements, also known as prepared statements, securely wrap SQL statements. Instead of placing data in a string being sent to a database, the locations of data in the string are replaced with a ? and then later set using programming methods. For example, the SQL command "select * from mytable where name='Michael'" would be replaced by the prepared statement "select * from mytable where name=?" and then a method, such as setString(1, "Michael"), would be used to concatenate the data to the string before sending the query to the database.