What Is SQL Injection

How does SQL-injection work and how do I protect against it

I cannot resist aswell.

SQL Injection is "a code injection technique that exploits a security vulnerability occurring in the database layer of an application". In other words it's SQL code injected in as user input inside a query.

SQL Injections can manipulate data (delete, update, add ecc...) and corrupt or delete tables of the database. I'm not aware of SQL Injections manipulating scripts though.

Let's say in your PHP script you are expecting (as user input) a username and a password from the login form that are later used inside a query such as:

SELECT Id FROM Users WHERE Name = $name AND Password = $password;

The user can insert inside $name and as $password whatever he likes (for example trough an <input>). Let's imagine he adds a name such as "1 OR 1 = 1; --", the query will now look like:

SELECT Id FROM Users WHERE Name = 1 OR 1 = 1; -- AND Password = $password;

and then, after the ; I could add another query or make the script think that the username and the password actually exists.

Notice that -- AND Password = $password; is a SQL comment and will therefore be ignored.

If you are using PHP < 5 then you should look for mysql_real_escape_string() and use it to escape user inputs before embedding it inside a query.

If you are using PHP5+ you should use PDO or the mysqli extension which can prevent this problem via prepared statements.

SQL injection will occur only when there is a form to submit?

SQL injection happens whenever you have data submitted by a user that you integrate into a query and pass to the server without checking it. This could just as easily occur with a GET parameter.

Consider if you have user pages at: /user/{userid}

Someone requests: /user/1;DROP TABLE users;

If you were building a query like this:

SELECT * FROM users WHERE id=$userId

... that query would now be:

SELECT * FROM users WHERE id=1;DROP TABLE users;

So you can see why this might be an issue.

What is SQL injection? And what is it use and plese give me a some real time example Regards & Thanks Hareesh

User input that deliberately contains SQL code to do harmful things, and isn't disabled or sanitized by the code. E.g.,

$who = $_GET['customer_id'];
 ...
DELETE from records WHERE customer_id = '$who'

could be injected with something similar to customer_id=1234' and 1=1 and ''=', resulting in

DELETE from records WHERE customer_id = '1234' and 1=1 and ''=''

resulting in all records in the table being deleted. It could be sanitized by escaping all ' in the user input.

How can I prevent SQL injection in PHP?

The correct way to avoid SQL injection attacks, no matter which database you use, is to separate the data from SQL, so that data stays data and will never be interpreted as commands by the SQL parser. It is possible to create an SQL statement with correctly formatted data parts, but if you don't fully understand the details, you should always use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

You basically have two options to achieve this:

Using PDO (for any supported database driver):

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
$stmt->execute([ 'name' => $name ]);

foreach ($stmt as $row) {
    // Do something with $row
}

Using MySQLi (for MySQL):

$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'
$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // Do something with $row
}

If you're connecting to a database other than MySQL, there is a driver-specific second option that you can refer to (for example, pg_prepare() and pg_execute() for PostgreSQL). PDO is the universal option.

Correctly setting up the connection

PDO

Note that when using PDO to access a MySQL database real prepared statements are not used by default. To fix this you have to disable the emulation of prepared statements. An example of creating a connection using PDO is:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8mb4', 'user', 'password');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

In the above example, the error mode isn't strictly necessary, but it is advised to add it. This way PDO will inform you of all MySQL errors by means of throwing the PDOException.

What is mandatory, however, is the first setAttribute() line, which tells PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it to the MySQL server (giving a possible attacker no chance to inject malicious SQL).

Although you can set the charset in the options of the constructor, it's important to note that 'older' versions of PHP (before 5.3.6) silently ignored the charset parameter in the DSN.

Mysqli

For mysqli we have to follow the same routine:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // error reporting
$dbConnection = new mysqli('127.0.0.1', 'username', 'password', 'test');
$dbConnection->set_charset('utf8mb4'); // charset

Explanation

The SQL statement you pass to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :name in the example above) you tell the database engine where you want to filter on. Then when you call execute, the prepared statement is combined with the parameter values you specify.

The important thing here is that the parameter values are combined with the compiled statement, not an SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something you didn't intend.

Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course). In the example above, if the $name variable contains 'Sarah'; DELETE FROM employees the result would simply be a search for the string "'Sarah'; DELETE FROM employees", and you will not end up with an empty table.

Another benefit of using prepared statements is that if you execute the same statement many times in the same session it will only be parsed and compiled once, giving you some speed gains.

Oh, and since you asked about how to do it for an insert, here's an example (using PDO):

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');

$preparedStatement->execute([ 'column' => $unsafeValue ]);

Can prepared statements be used for dynamic queries?

While you can still use prepared statements for the query parameters, the structure of the dynamic query itself cannot be parametrized and certain query features cannot be parametrized.

For these specific scenarios, the best thing to do is use a whitelist filter that restricts the possible values.

// Value whitelist
// $dir can only be 'DESC', otherwise it will be 'ASC'
if (empty($dir) || $dir !== 'DESC') {
   $dir = 'ASC';
}

SQL - What is the real danger of SQL injection?

SQL injection is not theoretical. There are news reports about real-world data breaches perpetrated using SQL injection practically every month. There's a great site that collects them: https://codecurmudgeon.com/wp/sql-injection-hall-of-shame/

Here's a good one from last month:

https://motherboard.vice.com/en_us/article/vba5nb/fornite-login-hack-epic-games-website

Bugs on Epic Games Site Allowed Hackers to Login to Any ‘Fortnite’ Player’s Account

...

That page, which is now offline, contained two vulnerabilities that are often found in websites: an SQL Injection (or SQLi), and a Cross-Site Scripting (or XSS), according to Check Point researchers.

Regarding the examples you give, I do recommend validating the password in the application code. Then you can differentiate between "no account found" vs. "account found, but password was wrong" (you don't want to reveal this to the user, but you might want to log the error, and possibly lock the account if they have too many failed password attempts).

But regardless, the SQL statement is vulnerable to SQL injection. Not only with the "OR 1=1" trick that you show, but also if you can trick the query into running UNION-based SQL queries:

$query = "SELECT * FROM users WHERE username='' UNION ALL SELECT * FROM INFORMATION_SCHEMA.TABLES -- '";
                                              ^^ $username ...

If the attacker can find an SQL query in your app (not necessarily the search by account name), they can use this technique to query all your tables. Then they can further use UNION techniques to query the data in all the tables, once they know their names.

Regarding your follow-up question:

"How does the data leave the back-end?"

Consider if your password-checking code (for the query above) looks like this:

$query = "SELECT id, username, password_hashed FROM users WHERE username='$username'";

$stmt = $pdo->query($query);
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
  if (!password_verify($password, $row['password_hashed'])) {
    die("Invalid login for user {$row['username']}");
  }
}

See? The result from the SQL query is output to the user. To the developer of this code, it seems obvious that since they just queried for $username then that's the value that would be returned by the query. They feel that $row['username'] is safe to use.

But it's not — it's some data from the other part of the UNION. By using CONCAT() and GROUP_CONCAT(), the attacker can even put together multiple columns from multiple rows. And they do. It may take them several tries to get their attack query to have the right number of columns in the right positions, but they apparently have nothing better to do.