Arlon's CSUMB Intro to Database Systems CST-363 Module 4 Learning Journal #4/#20 for the week Wed 05/19-Tues 05/25, year 2021

Arlon's CSUMB Intro to Database Systems CST-363 Module 4 Learning Journal #4/#20 for the week Wed 05/19-Tues 05/25, year 2021

Prompt for week 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?

   1. // Write the query as a string in MySQL
      String sql_query_string="SELECT * FROM `everything` 
      	 where name like ?; -- (etc.)";
      

   2. // Make a prepared statement with the string
      PreparedStatement preparedStatement =  connection.prepareStatement(
      		sql_query_string);
      

   3. // Replace the ?'s in the prepared statement with setString() and setInt()
      preparedStatement.setString(1, name.trim()+"%");

   4. // Execute the query with MySQL:
      ResultSet resultSet=preparedStatement.executeQuery();

   5. // Iterate the rows, get each column: 
      while (resultSet.next()) {
      	// Row details accessible here with ints like 1,2,3 1st, 
      	// 2nd, 3rd columns in that row:
      	String a=resultSet.getString(1), b=resultSet.getString(2), 
      			c= resultSet.getInt(3); 
      			// each row is here, columns are 
      			// 1, 2, 3 - like: 
      			// resultSet.getString(1) <-2,3 etc.
      }

   6. And if you want you can extrapolate all that and put it all in an ArrayList of HashMaps<String, String> 's like our group did. (I'll keep our algorithm secret.)
2. What is a parameterized SQL statement? That's where it has ?'s instead of actual parameters - so java automatically filters the input to filter for all kinds of injection attacks.
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?

   Mysql injection attacks are when a hacker puts executing code into an innocent form in order to exploit lack of validation and see results the programmer didn't intend anyone to see, which could likely be a security breach. The way it works is the java somehow filters the input when it replaces the ?'s with what the input will be. That way injection attacks are much less likely. Known attacks are filtered out. One I saw from my google search was just putting two dashes -- in a form - that (in the past) made the end of the query get commented out ( that would never work now it would just choke things ) but it shows how simple attacks could be if an attacker knew what to put in. Anyways parameterized statements filter out input so that can't happen as much, if parameterized statements are used to build the website.

Actual Journal Notes:

I thought it was funny I had to look up what 'fill a prescription' means...

I really like the ajaxy feel of dev in Spring Boot - not just the interface of what your program gets but how the interface of the program to built it works, too. I built it in Notepad++ of course and then to update the site it's literally one click on eclipse and it refreshes what I just did, and you don't even need to refresh the web page because it's what the page will request that matters and it's ready after one single click - pretty awesome - so amazing how it auto-reloads even if you save in another editor. They put some real thought behind this system, whoever made it. It works really good. I don't hate it, and that says a lot for a big framework like that. I don't know exactly how do deploy - for two reasons - how to get it hosting to port 80 - and how to port forward through multiple routers - I already talked the second one several times.

I liked how the assignment gave us object binding at the beginning and parameter passing for the second two and the extrapolation prompted by the sequence of questions landing us at

private java.util.ArrayList<java.util.HashMap<String,String>> arrayMapResultSQLTableGetter(
		Connection conn, String sql_start, String[] keys,String ... sql_params) throws SQLException{ /* !!! */ }

getting a whole table back in an ArrayList<java.util.HashMap<String,String>> for convenience.