Introduction
During the final week of my pairing tour, I had the opportunity to work with Colin J on two internal apps. On the first day of pairing, we migrated data from a MongoDB database to a relational database. Then, on the second day of pairing, we dove into a Middleman app to determine why assets were not being properly delivered to a web front-end. Although my pairing time with Colin was short, we covered a variety of topics including the pry gem, byte order marks, hex dumps, databases, and yak shaving.
One of the big ideas that I learned about during these two days was prepared statements. Our discussions about how to safely and efficiently migrate data touched on many of the concepts that I covered during my SOA project as an apprentice. Thus, I decided to dive into prepared statements for this blog post. The main resources that I referenced for this post include:
Insecure SQL (Don’t Use This Code!)
Suppose I build a web page that contains a form element. When a user types their username into the form, the webpage will send a GET request to some backend app. The backend app will search through a database and return some appropriate user data to the front-end app. This data will be nicely formatted and displayed on the screen. For example, if a user typed Bob into the form and clicked enter, the SQL query to the database might be:
SELECT * FROM users WHERE username = "Bob"
Notice how the SQL program takes the phrase ‘Bob’ and inserts it between two quotation marks. Then, the SQL statement searches the database for username ‘Bob’. As long as users of the database enter letters or numbers, this query will not cause any problems. However, suppose that the user accidentally hits the quotation mark key before submitting the form: Bob”
SELECT * FROM users WHERE username = "Bob""
The extra quotation mark makes the SQL statement invalid. Depending on how the app handles invalid SQL, the user will either gracefully receive an error message or watch the entire app crash. This type of behavior can certainly ruin the user experience on a website. However, something much more sinister can occur.
The problem here is that user input is being inserted directly to a SQL statement. In other words, there is a mixing of code and data without any sort of filtering step in-between. Thus, a malicious user can manipulate this situation in order to access the database in unintended ways. For example, suppose a malicious user types in the following phrase into the form: Bob”; DROP TABLE users; —
SELECT * FROM users WHERE username = "Bob"; DROP TABLE users; --"
The double dash represents a comment in SQL. Thus, the database will ignore the final quotation mark, yielding a valid SQL statement. This statement will correctly return the information for the row with username ‘Bob’… and then delete the entire users table!
Dropping database tables is a bad outcome, but things can get even worse. For example, a malicious user could enter the following phrase in the form: Bob” OR 1=1; —
SELECT * FROM users WHERE username = "Bob" OR 1=1; --"
Since 1=1 evaluates to true, the database will now return the records for all of the stored users. A malicious user can now access private user data, opening the app up to severe security breaches.
Prepared Statements
As stated above, SQL injections are caused (in part) because the SQL code and the user data intermingle upon the execution of a SQL statement without some sort of filtering process beforehand. The developer’s SQL code interprets the user’s input as an extension of the code rather than potentially unsafe data. Prepared statements solve the problem of SQL injection by sending the data and the code to the database separately.
A prepared statement is essentially a template for some SQL code. When a database session is started, a line of SQL code can be stored as a server-side object using the PREPAREcommand (in Postgres). If this command contains parameters, they can be defined by their position using $1, $2, $3, etc. For example:
PREPARE getuser (text) AS SELECT * FROM users WHERE username = $1;
The prepared statement can now be executed on Postgres with the EXECUTE command.
EXECUTE getuser('Bob');
Now, the argument to the prepared statement is interpreted as text. Even if a malicious user tries to inject some crafty SQL code, the program will differentiate the user input (the data) from the code itself (the statement). It is important to note that prepared statements are objects in memory, so they will only last for the duration of the current database session. If the session is every restarted, the developer must remember to recreate the prepared statement. Additionally, prepared statements cannot be used by multiple clients in simultaneous database sessions.
Due to the fact that prepared statements are objects stored in memory, they have been optimized to give performance advantages when one particular query is executed a large number of times. Thus, in addition to the added security of the query, there is often a performance boost when they are used.
Conclusion
A developer needs to think about database security when building a website. SQL injection is a common attack pathway that exploits the coupling of code and data within an app. The potentially unsafe data from a user can be isolated from database code by using prepared statements. Prepared statements can also (potentially) increase the performance of repetitive database queries.
Cyrus Vandrevala 