In general, various coding functions can help fight against SQL injection. In some examples coding functions are bypassed, and we need a safer coding function. Fortunately, database vendors usually provide some “guidance” on using safety functions.
For example, we usually code characters in MySQL like this:
NUL (0x00) –> \0 [This is a zero, not the letter O]
BS (0x08) –> \b
TAB (0x09) –> \t
LF (0x0a) –> \n
CR (0x0d) –> \r
SUB (0x1a) –> \z
” (0x22) –> \”
% (0x25) –> \%
‘ (0x27) –> \’
\ (0x5c) –> \\
_ (0x5f) –> \_
all other non-alphanumeric characters with ASCII values less than 256 –> \c
where ‘c’ is the original non-alphanumeric character.
Meanwhile, we can refer to the OWASP ESAPI implementation. This function is written by security experts and is more reliable:
ESAPI.encoder().encodeForSQL( new OracleCodec(), queryparam );
The function in use is
Codec ORACLE_CODEC = new OracleCodec();
String query = "SELECT user_id FROM user_data WHERE user_name = '" + ESAPI.encoder().encodeForSQL(ORACLE_CODEC, req.getParameter("userID")) + "' and user_password = '" + ESAPI.encoder().encodeForSQL(ORACLE_CODEC, req.getParameter("pwd")) + "'";
Finally, from the point of view of the database itself, you should use the principle of least privilege to avoid web applications from using highly privileged accounts like root and dbowner to directly connect to the database. If there are a number of different applications using the same database, you should also allocate each application a different account. Web applications using the database account should not have permission to create custom functions or manipulate the local file.