How to prevent SQL injection?

The articles written on this blog were created with the goal of teaching. Also, the information in the article may be incorrect, so please be careful.

SQL injection is not prevented by removing all quotes from the input. SQL commands and user data must be separated from the beginning so that the data is not interpreted as commands.

Use Prepared Statements

Prepared Statement or Parameterized Query separately marks the place where the value should be placed in the SQL statement.

const sql = 'SELECT id, name, password_hash FROM users WHERE name = ?';
const [rows] = await database.execute(sql, [userName]);

if (rows.length === 1)
{
    const passwordMatches = await passwordHasher.verify(
        rows[0].password_hash,
        password
    );
}

here?The value entered is transmitted as data, not SQL grammar. If the user uses quotation marks orOREven if you enter the same string, the query structure cannot be changed.

Passwords are not compared between plaintexts in SQL. After fetching user information as a parameter query, the input value is compared with a hash stored in a verified password hash library.

Using an ORM does not automatically make everything safer. The same vulnerability occurs if Raw Query is combined into a string without using the parameter function provided by ORM.

Table names and sorting criteria are checked against the allow list.

Parameters are generally used for values. table name, column name,ASCmeDESCIn many cases, the same SQL grammar position cannot be processed as a parameter.

These values ​​are not pasted as the user's string, but are selected from the Allowlist predetermined by the server.

const allowedSortColumns = {
    newest: 'created_at',
    name: 'name'
};

const sortColumn = allowedSortColumns[requestedSort] ?? 'created_at';
const sql = `SELECT id, name FROM users ORDER BY ${sortColumn} DESC`;

requestedSortIt is important to convert it to a safe string owned by the server, rather than putting itself into SQL.

Reduce database account privileges

If administrator privileges are granted to the database account used by the web server, damage from a single vulnerability can spread to the entire database.

• Functions that only perform searches are given read permission only.
• Access only the tables you need.
• Do not perform database management tasks using the web application account.
• Passwords are not stored in plain text, but using a verified hash method.

Errors are logged on the server and hidden from the user.

If you respond with a detailed SQL error as is, internal structures such as table names or column names may be exposed. A general failure message should be displayed to the user, and detailed errors should be logged in a server log with controlled access.

Input validation, firewall, and detection tools can also help. But the first thing to do is to parameterize all queries, limit dynamically entered SQL syntax to a whitelist, and minimize database permissions.