I received a security audit email near the end of work on Christmas day, presumably saying that there is a SQL injection vulnerability that needs urgent fix (WTF?). All right, Christmas plans gone. Let’s fix the leak.
Causes of SQL injection vulnerability
SQL Injection is the key to Web security. SQL Injection is the key to Web security.
SQL injection vulnerabilities occur when unchecked or inadequately checked user input data accidentally turns into code execution.
For historical reasons, the SQL statement executed is stored in the database. Each query will concatenate the SQL statement stored in the database with the parameters passed by the user, and then send it to the database engine for execution. For example:
// User query parameters
const query = {
pageSize: 10
}
let sqlStatement = `select * from users limit {{pageSize}}`
sqlStatement = sqlStatement.replace(/{{(\w+)}}/.value= >{
return query[value.slice(2, -2)] // Replace limit with 10
})
await client.query(sqlStatement)
Copy the codeAs you can see from the above code, there is not enough checking of the user’s data, so the above code is at risk of SQL injection!
An attacker can change the pageSize to large, and if there is a lot of data in the database, successive requests will block the database query, leaving no response for other users.
Let’s look at another piece of code
const name = "' or 1 =1--max '"
let sqlStatement = `select * from users where name= '${name}'`
await client.query(sqlStatement)
Copy the codeThere is still no verification of the user’s data in the program, and the name passed in by the user is directly spliced into THE SQL statement and launched a query.
As a result: The attacker closes the name in the original SQL statement where condition (name= “”) by passing single quotation marks, and then creates a true condition (or 1 = 1) that is always executed. Finally, the attacker comments out the following statements to prevent errors.
Query user information:
[{ name: 'max', age: 28 }]
Copy the codeAfter SQL injection, the attacker can obtain the user information of the whole site, resulting in the leakage of the user information of the whole site
[
{ name: 'max', age: 28 },
{ name: 'evle', age: 29 },
{ name: 'sangwoo', age: 30 }
]
Copy the codeRepair the holes
Now that we know what caused the bug, let’s fix it
Fix SQL injection:
They don’t trust their input. They don’t trust their input
Check parameter type
The data we receive from the network is all in string format, such as when we make a GET request with the pageSize parameter
curl 'http://localhost:3000? pageSize=10'
Copy the codeThen we can get the pageSize parameter in our program
{ pageSize: '10' }
Copy the codeIt is clear from console.log that pageSize is a string, and concatenating it directly into an SQL statement would create an SQL injection vulnerability, so we cannot assume that the user will enter only string integers, but restrict pageSize to integer types.
We need to convert pageSize to an integer, and we should also limit the maximum pageSize
const val = parseInt(query.pageSize, 10);
if (isNaN(val) || val > 50) {
throw new BadRequestException('Validation failed');
}
Copy the codeIf you use Nest.js as the node. js framework, Pipe can be used to easily convert and validate parameters.
As an example, we sometimes need to concatenate Boolean arguments into SQL statements. As long as the concatenated arguments are strings, there is a risk of SQL injection
const param = 'true malicious statement '
'select * from user where is_vip = ' + param
Copy the codeSo we need to really limit the user’s input, if it’s true or false,
const isVip = 'true malicious statement '
'select * from user where is_vip = ' + isVip
const isBoolean = isVip === 'true' || isVip === 'false'
if(! isBoolean) {throw new BadRequestException('Validation failed');
}
Copy the codeescape
How do you prevent SQL injection vulnerabilities for string arguments? For example, the SQL injection vulnerability statement
const name = "' or 1 =1--max '"
let sqlStatement = `select * from users where name= '${name}'`
await client.query(sqlStatement)
Copy the codeWe can use the SQLString library to translate the name, as stated in the README of the library:
In order to avoid SQL Injection attacks, you should always escape any user provided data before using it inside a SQL query
So let’s change our code
const name = "' or 1 =1--max '"
let sqlStatement = `select * from users where name= '${sqlstring.escape(name)}'`
await client.query(sqlStatement)
Copy the codeThe translated name will become
'\' or 1 =1--max \''
Copy the codeThis statement will report syntax errors when executing queries, preventing attackers from getting the data they want.
Sqlstring also provides a way to use prepared statements like mysql.
var sql = SqlString.format('UPDATE users SET foo = ? , bar = ? , baz = ? WHERE id = ? '['a'.'b'.'c', userId]);
Copy the codePrecompilation is also an effective way to prevent SQL injection. The point principle is to precompile SQL statements through placeholders. The value passed in by the user is treated as a pure value, which will not cause other logic to be generated in the compiled SQL statement.
Some databases don’t support precompilation, I use ClickHouse and I don’t know if it does…
Escape is translated using the same rules as the mysql escape function:
- Numbers are left untouched
- Booleans are converted to true / false
- Date objects are converted to ‘YYYY-mm-dd HH:ii:ss’ strings
- Buffers are converted to hex strings, e.g. X’0fa5′
- Strings are safely escaped
- Arrays are turned into list, e.g. [‘a’, ‘b’] turns into ‘a’, ‘b’
- Nested arrays are turned into grouped lists (for bulk inserts), e.g. [[‘a’, ‘b’], [‘c’, ‘d’]] turns into (‘a’, ‘b’), (‘c’, ‘d’)
- Objects that have a toSqlString method will have .toSqlString() called and the returned value is used as the raw SQL.
- Objects are turned into key = ‘val’ pairs for each enumerable property on the object. If the property’s value is a function, it is skipped; if the property’s value is an object, toString() is called on it and the returned value is used.
- undefined / null are converted to NULL
NaN / Infinity are left as-is. MySQL does not support these, and trying to insert them as values will trigger MySQL errors until they implement support.
The fifth point is crucial: string arguments are safely translated. Looking at line 202 of the source code, we see that some characters that might be executed by malicious injection are translated into unexecutable strings
var CHARS_ESCAPE_MAP = {
'\ 0' : '\ \ 0'.'\b' : '\\b'.'\t' : '\\t'.'\n' : '\\n'.'\r' : '\\r'.'\x1a' : '\\Z'.'"' : '\ \ "'.'\' ' : '\ \ \'.'\ \' : '\ \ \ \'
};
while ((match = CHARS_GLOBAL_REGEXP.exec(val))) {
escapedVal += val.slice(chunkIndex, match.index) + CHARS_ESCAPE_MAP[match[0]];
chunkIndex = CHARS_GLOBAL_REGEXP.lastIndex;
}
Copy the codeWrite it at the end
Node.js postgres driver PG provides precompiled queries to prevent SQL injection by default.
const text = 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *'
const values = ['brianc'.'[email protected]']
// callback
client.query(text, values, (err, res) = > {
if (err) {
console.log(err.stack)
} else {
console.log(res.rows[0])
// { name: 'brianc', email: '[email protected]' }}})Copy the codeMost database drivers now provide similar query methods, even if they don’t have sqL-proof injection drivers (such as the ClickHouse Driver I’m using). We can also prevent SQL injection attacks by encapsulating a secure Query method with SQLString.
query(query, values) {
constformattedQuery = sqlstring.format(query, values); . }Copy the codeWell, after 2 and a half hours of modification, finally checked all user parameters of the historical interface and made security treatment, also passed the inspection of security audit personnel.
If this article helped you, give it a thumbs up