Week Twenty - CST 363 - Database Systems

When coding a Java program that will perform a SELECT statement that return multiple rows,  what are the steps needed?  The first is to make a connection to the database and the last is close the connection.  What are the other steps?

1. Make a connection to the database
2. Create a PrepatedStatement object with the SQL SELECT statement
3. Set the parameters for the PrepatedStatement object with your Java variables
4. Execute the statement using a ResultSet object and the executeQuery() method - executeUpdate(), or execute() can be used for other types of SQL statements.
5. Process the ResulSet.
6. Close the connection.

What is a parameterized SQL statement? 

A parameterized query (also known as a prepared statement) is a means of pre-compiling a SQL statement so that all you need to supply are the "parameters" (think "variables") that need to be inserted into the statement for it to be executed. It's commonly used as a means of preventing SQL injection attacks.

Do a google search for "SQL injection attack".   What is an "injection attack" and how do parameterized statements help to prevent such security attacks?

SQL injection, also known as SQLI, is a common attack vector that uses malicious SQL code for backend database manipulation to access information that was not intended to be displayed. 

The root of the SQL injection problem is in the mixing of the code and the data. With prepared statements the data coming in from the user is treated as data - and there is no way it can be intermixed with your application SQL and/or be interpreted as SQL. The prepared statements "prepare" the SQL query first to find an efficient query plan and user data is guaranteed to never be executed.