Here are some PHP code samples demonstrating ways to prevent SQL injection attacks:
- 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
$stmt->execute();
// 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.
- 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.