How to Prevent SQL Injection in PHP
This wikiHow teaches you how to prevent SQL injection using Prepared Statements in PHP. SQL injection is one of the most common vulnerabilities in Web applications today. Prepared Statements use bound parameters and do not combine...
Part 1 of 2:
Understanding SQL Injection
- SQL Injection is a type of vulnerability in applications that use a SQL database. The vulnerability arises when a user input is used in a SQL Statement:
$name = $_GET['username']; $query = "SELECT password FROM tbl_user WHERE name = '$name' ";
- The value a user enters into the URL variable username will be assigned to the variable $name. It's then placed directly into the SQL statement, making it possible for the user to edit the SQL statement.
$name = "admin' OR 1=1 -- "; $query = "SELECT password FROM tbl_user WHERE name = '$name' ";
- The SQL database will then receive the SQL statement as follows:
SELECT password FROM tbl_users WHERE name = 'admin' OR 1=1 -- '
- This is valid SQL, but instead of returning one password for the user, the statement will return all the passwords in the table tbl_user. This is not something you want in your web applications.
Part 2 of 2:
Using mySQLi to Create Prepared Statements
- Create the mySQLi SELECT Query. Use the code below to SELECT data from a table using mySQLi Prepared Statements.
$name = $_GET['username']; if ($stmt = $mysqli->prepare("SELECT password FROM tbl_users WHERE name=?")) { // Bind a variable to the parameter as a string. $stmt->bind_param("s", $name); // Execute the statement. $stmt->execute(); // Get the variables from the query. $stmt->bind_result($pass); // Fetch the data. $stmt->fetch(); // Display the data. printf("Password for user %s is %sn", $name, $pass); // Close the prepared statement. $stmt->close(); }
- Note: The variable $mysqli is the mySQLi Connection Object.
- Create the mySQLi INSERT Query. Use the code below to INSERT data into a table using mySQLi Prepared Statements.
$name = $_GET['username']; $password = $_GET['password']; if ($stmt = $mysqli->prepare("INSERT INTO tbl_users (name, password) VALUES (?, ?)")) { // Bind the variables to the parameter as strings. $stmt->bind_param("ss", $name, $password); // Execute the statement. $stmt->execute(); // Close the prepared statement. $stmt->close(); }
- Note: The variable $mysqli is the mySQLi Connection Object.
- Create the mySQLi UPDATE Query. Use the code below to UPDATE data in a table using mySQLi Prepared Statements.
$name = $_GET['username']; $password = $_GET['password']; if ($stmt = $mysqli->prepare("UPDATE tbl_users SET password = ? WHERE name = ?")) { // Bind the variables to the parameter as strings. $stmt->bind_param("ss", $password, $name); // Execute the statement. $stmt->execute(); // Close the prepared statement. $stmt->close(); }
- Note: The variable $mysqli is the mySQLi Connection Object.
- Create the mySQLi DELETE Query. The below script is how to DELETE data from a table using mySQLi Prepared Statements.
$name = $_GET['username']; $password = $_GET['password']; if ($stmt = $mysqli->prepare("DELETE FROM tbl_users WHERE name = ?")) { // Bind the variable to the parameter as a string. $stmt->bind_param("s", $name); // Execute the statement. $stmt->execute(); // Close the prepared statement. $stmt->close(); }
- Note: The variable $mysqli is the mySQLi Connection Object.
5 ★ | 2 Vote
You should read it
- How to Write an Artist Statement
- How to Write an Artist's Statement
- How to retrieve a Tik Tok password when it is lost
- If ... Else in C
- How to remove PDF file password
- Check the security of the password
- Use an 8-character Windows NTLM password? Congratulations, your password may be unlocked after only 2.5 hours
- 5 best password management apps for iOS
- Has your password been leaked? Please check now
- How to Remove the Password from a Zip File Without Knowing the Password
- Set a password for the USB
- 5 steps to remove PDF password online