Sentry Answers>PHP>

How can I prevent SQL injection in PHP?

How can I prevent SQL injection in PHP?

Nadia S.

The ProblemJump To Solution

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.

The Solution

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.

Connect to the database using PDO

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:

Click to Copy
// 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.

Prepare and execute the SQL query

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:

Click to Copy
// 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]);

PHP input validation

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:

Click to Copy
$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.

Conclusion

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.

  • Syntax.fm logo
    Listen to the Syntax Podcast

    Tasty treats for web developers brought to you by Sentry. Get tips and tricks from Wes Bos and Scott Tolinski.

    SEE EPISODES

Considered “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.

© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.