Ways to Prevent SQL injections in PHP

Here are some PHP code samples demonstrating ways to prevent SQL injection attacks:

  1. Using Prepared Statements with Bound Parameters:

Prepared statements with bound parameters are the recommended approach to prevent SQL injection attacks. Here’s an example:

// Initialize a PDO instance
$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'username', 'password');

// Prepare a SQL statement with placeholders
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username');

// Bind values to the placeholders
$username = $_POST['username'];
$stmt->bindParam(':username', $username);

// Execute the statement

// Fetch the results
$results = $stmt->fetchAll();

In this example, the SQL statement contains a placeholder :username instead of directly including the user input. The user input is then bound to this placeholder using the bindParam() method.

  1. Using mysqli_real_escape_string():

While prepared statements are recommended, mysqli_real_escape_string() can also be used to prevent SQL injection attacks:

// Initialize a mysqli instance
$conn = mysqli_connect('localhost', 'username', 'password', 'mydb');

// Escape user input using mysqli_real_escape_string()
$username = mysqli_real_escape_string($conn, $_POST['username']);

// Build and execute the SQL statement
$sql = "SELECT * FROM users WHERE username = '$username'";
$result = mysqli_query($conn, $sql);

// Fetch the results
$results = mysqli_fetch_all($result, MYSQLI_ASSOC);

In this example, the user input is escaped using the mysqli_real_escape_string() function before including it in the SQL statement.

It’s important to note that while escaping input can help prevent SQL injection attacks, it’s not a foolproof solution and can still leave your application vulnerable. Prepared statements with bound parameters are the recommended approach.