CST 363 Intro to Database Systems Module 4 : Java Web Programs and DB

 

4.0 Review

This week we are learning about the integration between Java web programs and our database in SQL. We are also learning how to use Spring Boot which is a a framework developed for Java Web Applications. I've learned that a framework is a bunch of boilerplate code that is written to simplify a lot of the basic. setup you wold need to get a project started. Once the project is started, the framework contains a lot of code that makes common tasks for the project easier. Frameworks are designed with certain assumptions about what type of projects will be built with them and what kinds of tasks the user will perform. 

Our team edited our past week's assignment database in order to make it fit with this week's project. I learned how to integrate SQL query searches in a Java program in order to look up something in a database and returning the web page with the correct information. I learned how to use Java class in order to create forms.

4.1 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?  

You need to create a List/ArrayList first. Then, you need to create a ResultSet object with your SQL query. Third, you need to create a while loop that traverses the ArrayList/List to add to your object. This has to be encased in a try catch block.

4.2 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" 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.

A parameterzied query select statement returns a ResultSet which is used to retrieve the data by column and by row. An update/insert/delete statement returns the count of rows modified. 

We also use parametrized markers ? as a placeholder in the SQL statement as a placeholder. 

4.3 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 happens when you interpolate some content into a SQL query string, and the result modifies the syntax of your query in ways you didn't intend. It doesn't have to be malicious, it can be an accident. But accidental SQL injection is more likely to result in an error than in a vulnerability. The harmful content doesn't have to come from a user, it could be content that your application gets from any source, or even generates itself in code.

The best method to prevent SQL injections is to use prepared statements. With these, you send a query to the SQL database such as:

"SELECT * FROM `users` WHERE `username` = '?'";

This lets the database know the format of the query (WHERE username equals some value), so there is no confusion when given a plain text query. Then the database knows to expect one value, and where to put it. Then you pass that value to the database which it can use to search. This is also better as the database can optimize the query for faster searching.