Table of Contents

Connecting Angular Apps to MySQL

Kat is 2023-2024 President of Sigma Psi Zeta (SYZ) Sorority at RIT. Used on a post about Connecting Angular Apps to MySQL.

In Introduction to Software Engineering (SWEN-261, Spring 2022) at the Rochester Institute of Technology, my team built ReadingRoom, an online bookstore with an Angular front end and a Java (Spring) API. This post shows how we connected that app to a MySQL database running on a Raspberry Pi, and what we would do differently today.

TLDR

We originally wrote this post to help faculty see how one group of students added a MySQL database to the class project.

Creating the Database

First, a database needs to be created and configured. You can learn how we did this here. 

Set up a MySQL Database on a Raspberry Pi Server

Next, we set up a server using a Raspberry Pi. We wanted to edit the database from any device, so we used a server instead of hosting the files locally. That way, data persisted and every user saw the same, current data. Learn how we built a database server on a Raspberry Pi here. 

Connecting the Java API to MySQL

After setting up the server, we connected the app to it. Angular, a platform for building mobile and desktop web applications, runs the front end. The Java API behind it is what reads from and writes to MySQL. See how we did this below. 

Our Code

We used the @JsonProperty Jackson annotation a lot. It lets non-standard getter and setter methods work with JSON. For example, @JsonProperty converts the following Java code to and from JSON (Stack Overflow).

public class Parameter {
@JsonProperty("Name")
public String name;
@JsonProperty("Value")
public String value;
}
"Parameter":{
  "Name":"Parameter-Name",
  "Value":"Parameter-Value"
}

🌸👋🏻 Enjoying this post? Get the next issue of [ret]2read. Once a month, I’ll email you whatever’s on my mind, usually Apple security research, offensive security, open source software freedom, and operating system internals.

Account Objects

The Account.java class provides a constructor declaration for account objects (aObj). It also provides getters and setters for some aObj attributes. Please view the Account.java file on GitHub to gain some context before reading the rest of this section. If needed, GeeksforGeeks has a great article on Classes and Objects in Java, which provides additional context for how we set up this class.

First, instance variables are defined at the top of the class. These variables are used in Account.java’s methods (i.e., getters, setters, and during aObj creation).

@JsonProperty("LastName")
private String lastname;
@JsonProperty("FirstName")
private String firstname;
@JsonProperty("PhoneNumber")
private String phonenumber;
@JsonProperty("Username")
private String username;
@JsonProperty("Password")
private String password;
@JsonProperty("Role")
private int role; // user: 1, guest: 2, admin: 0

Next, a constructor declaration is created. The constructor creates an aObj.

/**
* Creates account entity according to provided authentication
*
* @param lastname: account user's lastname
* @param firstname: account user's firstname
* @param phonenumber: account user's phone number
* @param username: account username
* @param password: account password provided as json object
* @return Account: returns itself as object
*/
public Account(@JsonProperty("LastName") String lastname,
@JsonProperty("FirstName") String firstname,
@JsonProperty("PhoneNumber") String phonenumber,
@JsonProperty("Username") String username,
@JsonProperty("Password") String password) {
this.lastname = lastname;
this.firstname = firstname;
this.phonenumber = phonenumber;
this.username = username;
this.password = password;
this.role = (this.username.equals("admin")) ? 0 : 1;
}

The constructor declaration includes the contents for the user to enter. For example, the required attributes are LastName, FirstName, Username, and Password. PhoneNumber is optional, but it is listed anyway. These attributes correspond to fields in the MySQL database, specifically the user table. 

MySQL user table description: UserID, LastName, FirstName, PhoneNumber, Username, Password, RoleNumber

However, some fields in the MySQL table do not correspond to attributes in Account.java. For example, the UserID field is nonexistent in Account.java. UserID is a required field that uses auto-increment. In other words, the MySQL database, not the user, defines the account’s UserID. As a result, we do not reference UserID in Account.java (nor do we display a UserID textbox in the user interface during account creation). 

Other times, attributes are manually assigned. For example, the role attribute in Account.java (RoleNumber in the user table) is manually assigned if it is not the default value. Manually assigning attributes is bad practice, but this was the best we could do given the project’s time constraints.

The setup for the Book.java and Cart.java classes was almost identical.

Want your company or product in front of [ret]2read readers?
One sponsor per issue →

LoginDatabaseDAO.java

Next, we implemented LoginDatabaseDAO.java. This class imports Account.java, and it contains the methods used for registering an account and logging in.

First, we created a method to connect to the database server. The database server requires a generic login for all users.

/**
* Creates an instance of LoginDatabaseDAO
*
* Used for specificying the functionality of overriden functions
* checkCredentials() and register() that gets dependency injected
* into the RESTController element
*/
public LoginDatabaseDAO(){
try {
// the URL connects to the database as user=estore and password=password
this.connect = DriverManager
.getConnection("jdbc:mysql://smt5541pi.student.rit.edu:3306/book?"
+ "user=estore&password=password");
} catch (SQLException e) {
e.printStackTrace();
}
}

One flaw is that we did not implement account permissions. As a result, any account type can modify the database from the command line. For example, if a non-administrator executed the login command in a bash shell, they could alter the database with administrator permissions.

The server is down, and the password is changed, so no one can log in. However, we can start up the server for demonstrations if needed.

Next, we developed a method for account registration. You can see that register() has parameters for each field needed to create an account. Then, it builds a MySQL statement by concatenating a string with the arguments passed into the function. Lastly, the program sends a query to the database, and the server returns a boolean, indicating if the registration was successful.

/**
* Attempt to insert a new account into the database and get the response
* @param acc – Account object from the user that gets added to the database
* @return – the status of the success of database insertion
*/
@Override
public boolean register(Account acc) {
//send the username account stuff into the database
//THE COMMAND, swap the data values with the acc.*getters
//example command: INSERT INTO user (LastName, FirstName, PhoneNumber, Username, Password) VALUES ("Gallucci", "Olivia", "(123) 456-7890", "oliviagallucci", "password");
// create the command
String query = String.format(
"INSERT INTO user (LastName, FirstName, PhoneNumber, Username, Password)" +
" VALUES ('%s', '%s', '%s', '%s', '%s')",
acc.getLastname(), acc.getFirstname(), acc.getPhonenumber(),
acc.getUsername(), acc.getPassword());
System.out.println(query);
try {
Statement st = this.connect.createStatement();
int rs = st.executeUpdate(query);
System.out.println("updateResult");
System.out.println(rs);
//if account not created – 0, no affected rows.
if (rs != 0){
System.out.println("pass return nothing check");
// the row count for 1 element is 1 affected row = 1
if(rs == 1){
System.out.println("pass row count check");
return true;
}
}
} catch (SQLException e) {
e.printStackTrace();
return false;
}
//if ERROR/conflict returned – unsuccessful
//If correct – successful.
return false;
}

To complete this file, we created a method for users to log in to their respective accounts. Each account had a shopping cart, personalized information, etc. 

/**
* Call the database in here and get the response of log in or not
* @param acc – Account object from the user where the password is
* checked according to the database
* @return – status of credential checking
*/
@Override
public boolean checkCredentials(Account acc) {
// send a command to get the user credentials from the database
//THE COMMAND
String query = String.format("select Password from user WHERE UserName='%s'",acc.getUsername());
// print the query
System.out.println(query);
try {
Statement st = this.connect.createStatement();
ResultSet rs = st.executeQuery(query);
while (rs.next()){
System.out.println("queryResult");
// parse the returned result to get the pure password.
this.queryResult = rs.getString(1);
System.out.println(this.queryResult);
}
} catch (SQLException e) {
e.printStackTrace();
return false;
}
//if username not found – returns null OR empty string.
//no account with that username – return false
if (queryResult != null || !queryResult.equals("") ){
System.out.println("pass empty check");
// check the returned password and the acc.getpassword, if same return true, if not false
if(this.queryResult.equals(acc.getPassword())){
System.out.println("pass password check");
return true;
}
}
System.out.println("last false");
return false;
}

LoginDatabaseDAO.java is similar to HelperSQL.java (creates book queries for interacting with the database) and CartDatabaseDAO.java (modifies items in users’ shopping carts).

LoginDAO.java

To complete this series of files, we created LoginDAO.java, which defines the interface for the log-in feature and Account object persistence.

Other

Other files that are less relevant to this post but relevant to the project are InventoryEntry.java (find usage level and other stats about a book) and EstoreDatabaseDAO.java (add, update, delete, filter, search, and sort books).

What we would fix today

  • Use prepared statements. register() pastes user input straight into the SQL string, so a crafted username could run its own SQL (SQL injection). A PreparedStatement with ? placeholders keeps input as data.
  • Hash passwords. The user table stored passwords as plain text. Store a slow, salted hash (for example, bcrypt or Argon2) instead.
  • Keep credentials out of code. The database username and password lived in the Java source. Load them from environment variables or a config file that is not committed.
  • Use least privilege. Give the API a database account that can only do what it needs, and check user roles in the API before each change.

Conclusion

Questions? Reach out.

Portrait of Olivia Gallucci in garden, used in LNP article.

Written by Olivia Gallucci

Olivia is senior security engineer, certified personal trainer, and freedom software advocate. She writes about offensive security, open source software, and professional development.

Discover more from [ret]2read

A Monthly OS Internals Publication by Olivia Gallucci. Subscribe now to keep reading and get access to the full archive.

Continue reading