SQL 注入

In this tutorial you will learn how to fix the common database vulnerabilities.

## What is SQL Injection?
SQL injection is an attack wherein an attacker can inject or execute malicious SQL code via the input data from the browser to the application server, such as web-form input.

It can be used to expose sensitive information like user's contact numbers, email addresses, credit card information and so on. An attacker can even use it to bypass authentication process and get access to the entire database. Let's see how it actually works.

## How SQL Injection Works
Consider the following SQL statement which is a simple example of authenticating a user with a username and password in a web application.

```sql
SELECT * FROM users WHERE username='username_val' AND password='password_val';
```
Here, username_val and password_val represents the username and password entered by the user respectively. If a user enters the values such as "john" as username and "123" as password, then the resulting statement will be:

```sql
SELECT * FROM users WHERE username='john' AND password='123';
```
But suppose, if user is an attacker and instead of entering a valid username and password in the input fields, he entered the values something like: `' OR 'x'='x`

In this case, the above SQL query will be constructed as:

```sql
SELECT * FROM users WHERE username='' OR 'x'='x' AND password='' OR 'x'='x';
```
This statement is a valid SQL statement and since WHERE 'x'='x' is always true, the query will return all rows from the users table. You can see how easily an attacker can get access to all the sensitive information of a database with just a little dirty trick.

If the users table is quite large and contains millions or rows, this single statement can also lead to denial-of-service attack (DoS attack) by overloading the system resources and make your application unavailable for legitimate users.

<div class="callout callout-danger mb-3">Warning: The consequences of ignoring SQL injection vulnerability can be even worse if your script generates a DELETE or UPDATE query. An attacker can delete data from the table or change all of its rows permanently.</div>

## Preventing SQL Injection
Always validate user input and make no assumptions. Never build SQL statements directly from user input. If you're using PHP and MySQL you can use `mysqli_real_escape_string()` function to create a legal SQL string that you can use in an SQL statement.

Here's a very basic example of user authentication using PHP and MySQL that demonstrates how to prevent SQL injection while taking input from users.

```sql
<?php
// Starting session
session_start();
 
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");
 
// Check connection
if($link === false){
    die("ERROR: Could not connect to database.");
}
 
// Escape user inputs for security
$username_val = mysqli_real_escape_string($link, $_POST['username']);
$password_val = mysqli_real_escape_string($link, $_POST['password']);
 
if(isset($username_val, $password_val)){
    // Attempt select query execution
    $sql = "SELECT * FROM users WHERE username='" . $username_val . "' AND password='" . $password_val . "'";
    if($result = mysqli_query($link, $sql)){
        if(mysqli_num_rows($result) == 1){
            // User is authenticated do your stuff here
            $row = mysqli_fetch_array($result);
            /* Holding values in session variable so that it can be
            accessed later within the same session reference */
            $_SESSION['user_id'] = $row['user_id'];
            $_SESSION['first_name'] = $row['first_name'];
            header('Location: welcome.php');
        } else{
            echo "ERROR: Invalid username or password.";
        }
    } else{
        echo "ERROR: Something went wrong. Please try again.";
    }
}
 
// Close connection
mysqli_close($link);
?>
```
Please check out the tutorial on PHP MySQL prepared statements to learn the advanced techniques of preventing SQL injection in your web applications.

<div class="callout callout-info mb-3">Tip: Test the size and type or content of the data that is received by your application and enforce appropriate limits to protect against system resources exploitation.</div>