Nadia S.
—SQL injection is a common security vulnerability where SQL commands (or code) are entered into user input fields to be executed by the database server. From reading, updating and deleting data to issuing commands to the operating system, attackers use SQL injection to maliciously interact with sensitive data.
Preventing SQL injection in PHP should consist of a layered approach of using prepared statements, input validation, limiting database admin privileges, and regularly updating PHP and SQL software.
We’ll look at how you can create prepared statements that separate SQL commands from data using the PDO PHP extension. This solution is robust and reliable. First, we’ll create a database connection using PDO. Next, we use the PDO connection object to handle our SQL database query with user input.
Also, we’ll briefly look at PHP form input validation which alone isn’t reliable for preventing SQL attacks, but provides an additional layer of security.
When we make a PDO database connection, it’s important to remember that prepared statement emulation combines the SQL query with values into a single string. We need to separate them so we must disable the use of prepared statement emulation.
We set the PDO::ATTR_EMULATE_PREPARES
attribute to false
. For example:
// credentials $host = 'localhost'; $dbName = 'sample_db'; $myUsername = 'root'; $myPassword = 'pasword'; try { // connect to the database $pdo = new PDO("mysql:host=$host;dbname=$dbName", $myUsername, $myPassword); // disable emulated statements $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); // enable PDO error mode to throw exceptions $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { echo 'Connection failed: ' . $e->getMessage(); }
For debugging purposes, we also enable PDO error mode and exception throwing. To do this, we pass the PDO::ATTR_ERRMODE
and PDO::ERRMODE_EXCEPTION
arguments in the setAttribute
method.
Let’s say we have a login form that uses a SQL query to determine if an entered username and password combination exists in our database.
We use our pdo
connection object with the prepare
method to prepare a SQL statement with :username
and :password
as placeholders for the user data. The SQL query is parsed by the server as a prepared statement. Next, the user-entered data is bound to the placeholders using the execute
method. For example:
// get the user input from login form $username = $_POST['username']; $password = $_POST['password']; // SQL query with placeholders for username and password $sqlQuery = "SELECT * FROM users_test WHERE username = :username AND password = :password"; // create a prepared statement of the query $stmt = $pdo->prepare($sqlQuery); // bind the user input to the prepared statement's parameters $stmt->execute(['username' => $username, 'password' => $password]);
We can use a regular expression to check if the user’s input matches the expected input. However, malicious input could still pass these validation checks making this technique an inadequate defense when used by itself.
For example, we can validate that an entered username is between three and 10 characters long, and only contains letters, numbers, hyphens, and underscores.
We use the preg_match()
function with the/^[A-Za-z0-9_-]{3,10}$.
regular expression as the first argument and the user input as the second argument. If the input matches the regular expression, we assign the input to another variable for use in our PDO prepared statement as above. For example:
$userInput = $_POST['username']; $username = ''; if (preg_match('/^[A-Za-z0-9_-]{3,10}$/', $userInput)) { echo 'Valid username'; $username = $_POST['username']; } else { echo 'Username can only contain letters, numbers, underscores, and hyphens. It must be between 3 and 10 characters long.'; }
We can use a regular expression generator to create a regular expression for the password, or other input fields, as well.
A powerful way to prevent SQL injection is to use the PDO database extension to create prepared statements with placeholders for variable binding.
Instead of the user’s input being substituted into a SQL query directory, placeholders are used to represent the input in a template-like statement. Then, user input is bound to the statement, separating it from the SQL query.
Validating user input using regular expressions can help defend against SQL injection attacks; however, it can’t be solely relied on.
Tasty treats for web developers brought to you by Sentry. Get tips and tricks from Wes Bos and Scott Tolinski.
SEE EPISODESConsidered “not bad” by 4 million developers and more than 100,000 organizations worldwide, Sentry provides code-level observability to many of the world’s best-known companies like Disney, Peloton, Cloudflare, Eventbrite, Slack, Supercell, and Rockstar Games. Each month we process billions of exceptions from the most popular products on the internet.
Here’s a quick look at how Sentry handles your personal information (PII).
×We collect PII about people browsing our website, users of the Sentry service, prospective customers, and people who otherwise interact with us.
What if my PII is included in data sent to Sentry by a Sentry customer (e.g., someone using Sentry to monitor their app)? In this case you have to contact the Sentry customer (e.g., the maker of the app). We do not control the data that is sent to us through the Sentry service for the purposes of application monitoring.
Am I included?We may disclose your PII to the following type of recipients:
You may have the following rights related to your PII:
If you have any questions or concerns about your privacy at Sentry, please email us at compliance@sentry.io.
If you are a California resident, see our Supplemental notice.