PHP Security: Preventing SQL Injection

Person sits at desk writing computer software code

In the year 2000, I attended my first coding Bootcamp, though I don’t think they even had a term for them back then. The program lasted one month. It was an in-depth training course, where I learned the operating system Linux. The end goal of the course was to become a Certified Linux System Administrator. Upon certification, I was offered a job as a Linux System Administrator. Shortly after my employment began, the company went bankrupt, and I was let go. However, during my time there I became familiar with Open Source Software. I also started exploring the LAMP (Linux, Apache, MySQL, PHP) stack quite a bit, resulting in an affinity for PHP.

PHP is a general-purpose scripting language created in 1994 by Rasmus Lerdorf. It is a server-side language, meaning the code is executed on the server, generating HTML, which is then displayed on the client-side.1 It first stood for Personal Home Page, but now is a recursive acronym that stands for PHP: Hypertext Preprocessor. It is good for collecting form data, generating dynamic page content, and sending and receiving cookies.2 It can be embedded in HTML and is especially well-suited for web development. So much so that almost 80% of the websites on the web today use PHP.3

Whether they are used on the web or not, most software languages built today use the concept of Secure-by-Design. This means the software is designed from its foundation to be secure.4 PHP is not one of the languages built using this concept, and as a result, has many vulnerabilities and security holes. The good news is that there are methods to close these holes and reduce any inherent security risks. My goal here is to create a series of blog posts that will teach you how to do just that.

To help us with this task, there is a foundation called the Open Web Application Security Project (OWASP). They work "to improve the security of software... and are the source for developers and technologists to secure the web.”5 OWASP publishes a Top Ten Security Risks list, which “represents a broad consensus about the most critical security risks to web applications.”6 You can see the list here.

For the purposes of this first blog post about PHP Security, I thought it would make the most sense to start at the top of OWASP’s Top Ten Security Risks list and give solutions on how to prevent them. According to the list, the number one risk is injection. “Injection occurs when untrusted data is sent to an interpreter as part of a command or query.”7 The most common form of injection is SQL Injection. This is for two major reasons: 

1) The significant prevalence of SQL Injection vulnerabilities

2) The attractiveness of the target (i.e. the database typically contains all the interesting/critical data for your application)8

Perhaps you’ve seen the Bobby Tables comic strip:

pasted image 0.png

This tongue-in-cheek cartoon illustrates how easy it is for a clever hacker to enter a well-crafted query or script into a form. If the input is not sanitized, there could be dire consequences.

The best way to prevent something like this, and to avoid SQL Injection altogether is to use Prepared Statements in your database queries. There are two stages to a prepared statement: prepare & execute. During the “prepare” stage, “a statement template is sent to the database server.”9

Because I previously mentioned the LAMP stack, I will use MySQL to demonstrate the use of prepared statements. With PHP, there are three APIs that you can use to connect to a MySQL database. The first API shares its name with the MySQL database, and has been deprecated since PHP version 5.x and has no longer been included after version 7.x, so we will not be discussing it here. Therefore, the two remaining options are MySQLi and PHP Data Objects (PDO). The only real difference between the two is that MySQLi contains more specialized features unique to working with MySQL, while PDO can be used with many different types of databases. Below are examples of how to write prepared statements using the MySQLi and PDO APIs.

Example using MySQLi:

undefined

In the MySQLi example above, the template is using the ‘?’ as a placeholder in the VALUES list of the query. As you will see, using PDO, the placeholder is in the form of the parameter name with a ‘:’ in front of it.

Example using PDO:

undefined

 The “prepare” stage is followed by the “execute” stage. During the “execute” stage, parameter values are bound to the query and sent to the server.

Example using MySQLi:

undefined

In the above example, the template uses the bind_param method to bind the integer (signified by the “i”) stored in the $id variable to the ‘?’. As you will see, PDO uses the bindParam method to bind the parameter stored in the $value variable to ‘:value’, and then uses the execute() method to send the query to the server.

Example using PDO:

undefined

This method of using parameterized queries is safe because “bound variables are sent to the server separately from the query and thus cannot interfere with it.” Additionally, the server uses the values at the point of execution, after the statement template is parsed. Also, “bound parameters do not need to be escaped as they are never substituted into the query string directly.”10

Now that you know how to protect against SQL Injection, get out there and update your queries to include Prepared Statements. And be sure to join me in the next post, where I will tackle the second item on the OWASP Top Ten Security Risk list, Broken Authentication. This occurs when authentication and session management are implemented incorrectly, allowing attackers to compromise passwords, keys or session tokens. I look forward to teaching you how to protect against this type of attack, and helping make your PHP site more secure in the process.

https://www.php.net/manual/en/intro-whatis.php

https://www.php.net/manual/en/intro-whatcando.php

https://kinsta.com/blog/is-php-dead/

https://phpsecurity.readthedocs.io/en/latest/_articles/PHP-Security-Default-Vulnerabilities-Security-Omissions-And-Framing-Programmers.html

https://owasp.org/

https://owasp.org/www-project-top-ten/

https://owasp.org/www-project-top-ten/

https://github.com/OWASP/CheatSheetSeries/blob/master/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.md

https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

Contact Us!

If you're interested in learning more about Techtonic please Contact Us today! Techtonic is the premier partner for developing onshore, commercial-grade software. We've also developed a unique method to simultaneously close the talent gap that exists within technology, by tapping into a new and highly diverse workforce. Our model is pioneering the way clients develop and hire top talent, and we'd love to work with you!