Irrespective of the user input, runtime variables name and pass cannot affect the behavior of the query. Note that the use of only the PreparedStatement object isn’t a good defense. It must be used along with parameterization feature (“?”) for all runtime elements. Without the parameterization feature, string concatenation leads to SQL injection, even in the presence of the PreparedStatement object.
Stored procedures. Stored procedures are the SQL statements defined and stored in the database itself and then called from the application. Developers are usually only required to build SQL statements with parameters that are automatically parameterized. However, it’s possible for a developer to generate dynamic SQL queries inside stored procedures. Implement stored procedures safely by avoiding dynamic SQL generation inside.
Input validation. A common source of SQL injection is maliciously crafted external input. As such, it’s always a good practice to only accept approved input—an approach known as input validation. To protect against it, there are two variants of input validation: avoid list validation and preferlist validation.
Avoid listvalidation tests the external input against a set of known malicious inputs. An application compiles a list of all malicious inputs, and then verifies the external input against the compiled list. Therefore, it’s easy for an attacker to bypass avoid list validation since they can come up with multiple variants of malicious input that may not be part of the complied avoid list.
Preferlisting is a much better approach to mitigate risk. Preferlist validation tests an external input against a set of known, approved input. With prefer list input validation, the application knows exactly what’s desired and rejects other input values that fail a test against the known, approved input.
Principle of least privilege. This is a standard security control that helps minimize the potential damage of a successful attack. Application accounts shouldn’t assign DBA or admin type access onto the database server. Additionally, depending on access requirements, they should be restricted to least privileged access. For example, accounts that only require read access are only granted read access to the table they need to access. This ensures that if an application is compromised, an attacker won’t have the rights to the database through the compromised application.