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
- Angular never talks to MySQL directly. It calls the Java API, and the API’s DAO classes send SQL to the database over JDBC.
- Steps: create the database, host it on a Raspberry Pi, map tables to Java classes, then write DAO classes that run the queries.
- Lessons: our code built SQL from strings (open to SQL injection), stored plain-text passwords, and shared one database login. See what we would fix.
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).
"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).
Next, a constructor declaration is created. The constructor creates an aObj.
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.

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.
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.
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.
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
PreparedStatementwith?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.


You must be logged in to post a comment.