What is SQL injection?
SQL injection is a dangerous attack used by hackers and is the most common attack hackers use to attack your website. Attackers use malicious code on the server by injecting malicious SQL commands in the database query. Attackers can gain sensitive information from the server and even can modify it.
Preventing SQL injection in Php
To prevent SQL injection attacks, the first step you have to do is to separate data from the SQL commands. This helps to ensure the data is only as values and not executable commands.
Using Prepared Statements and Parameterized Queries
These are special SQL queries where the SQL commands and the parameters (data) are sent to the database separately. The database server parses the query first, then it treats any parameter placeholders as data only. Even if there is malicious code is passed, it will be treated only as plain text.
Example in Php with pdo
$pdo = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username');
$stmt->execute(['username' => $userInput]);Here :username is the placeholder. $iuserInput actual value is bound to this placeholder and it will be only treated as plain text, not SQL code so no malicious code can execute.
Always default to prepared statements
Prepared statements are the most reliable and simplest method to avoid SQL injection. Instead of trying to manually sanitize input, prepared statements ensure safety. It is so risky to use manual Input sanitization to handle all possible ways that user can enter the malicious code. Because different databases interpret inputs differently these sanitization methods might fail to account for all the changes.
Conclusion
Prepared statements and parameterized queries ensure SQL commands remain commands and user input remains data so SQL parser will never interpret as part of the SQL query. This separation helps to eliminate the main cause of SQL injection attacks and make your application secure against these harmful attacks.