The importance of protecting our websites against injection attacks SQL
is highly recommended to keep our data safe. Good practice recommends using prepared queries to avoid these attacks. But this is where I am going, does it exist or do you know of any example where, even if prepared queries are used, an attack of this type can be carried out?
I put this code as an example:
$server = "localhost";
$user = "usuario";
$password = "password";
$dbname = "ejemplo";
// Conectar
$db = new mysqli($server, $user, $password, $dbname);
// Comprobar conexión
if($db->connect_error){
die("La conexión ha fallado, error número " . $db->connect_errno . ": " . $db->connect_error);
}
// Preparar
$stmt = $db->prepare("INSERT INTO Clientes (nombre, ciudad, contacto) VALUES (?, ?, ?)");
$stmt->bind_param('ssi', $nombre, $ciudad, $contacto);
// Establecer parámetros y ejecutar
$nombre = "Donald Trump";
$ciudad = "Madrid";
$contacto = 4124124;
$stmt->execute();
$nombre = "Hillary Clinton";
$ciudad = "Barcelona";
$contacto = 4665767;
$stmt->execute();
// Mensaje de éxito en la inserción
echo "Se han creado las entradas exitosamente";
// Cerrar conexiones
$stmt->close();
$db->close();
It is a doubt that I have for a future web security project, where several attacks will be applied to test the vulnerability of the site.
Greetings.
As I already said in a comment, a library could be written on the subject and it would never be 100% elucidated given the diversity of possible scenarios, of APIs that can be used to connect, of database managers, of possible environments... .
However, I understand that the question seeks to shed some light on other measures that can be observed to prevent SQL Injection , apart from the use of prepared queries.
As a contribution, I will put some indicators of OWASP, an authority regarding security in general.
In SQL Injection Prevention Cheat Sheet they consider a primary level of defense and an additional level of defense (in the link there are examples of safe code according to the languages/handlers) and at the end a series of links if you want to go deeper into the subject) :
Primary defenses:
Additional Defenses:
primary defenses
Defense Option 1: Prepared Statements (with parameterized queries)
Using prepared statements with variable binding (also known as parameterized queries) is the way all developers should first be taught how to write database queries. They are easy to write and easier to understand than dynamic queries. Parameterized queries force the developer to define all the SQL code first, and then pass each parameter to the query later. This style of coding allows the database to distinguish between code and data, regardless of what user input is provided.
Prepared statements ensure that an attacker cannot change the intent of a query, even if the SQL commands are inserted by an attacker. In the safe example below, if an attacker were to enter the username of
tom 'o' 1 '=' 1
, the parameterized query would not be vulnerable and would instead look for a username that literally matches the stringtom 'o' 1 '=' 1
.Specific language recommendations:
In rare circumstances, prepared statements can harm performance. When faced with this situation, it is better to either a) forcefully validate all data or b) remove all user-supplied input using an escape routine specific to your database vendor as described below, rather than using a prepared statement.
Defense Option 2: Stored Procedures
Stored procedures are not always safe from SQL injection. However, certain standard stored procedure programming constructs have the same effect as using parameterized queries when implemented safely, which is the norm for most stored procedure languages.
They require the developer to simply create SQL statements with parameters that are automatically parameterized unless the developer does something out of the ordinary. The difference between prepared statements and stored procedures is that the SQL code for a stored procedure is defined and stored in the database itself and then called from the application. Both techniques are equally effective in preventing SQL injection, so your organization must choose which approach is best for you.
Note: 'Safely implemented' means that the stored procedure does not include any unsafe dynamic generation of SQL. Developers do not typically generate dynamic SQL within stored procedures. However, it can be done, but should be avoided. If it cannot be avoided, the stored procedure must use input validation or proper escaping as described in this article to ensure that all user-provided input to the stored procedure cannot be used to inject SQL into the stored procedure. dynamically generated query. Auditors should always look for uses of sp_execute, execute, or exec within SQL Server stored procedures. Similar audit trails are required for similar functions for other vendors.
There are also several cases where stored procedures can increase risk. For example, in MS SQL server, you have 3 main default roles: db_datareader, db_datawriter, and db_owner. Before stored procedures came into use, DBAs would grant db_datareader or db_datawriter web service user rights, depending on requirements. However, stored procedures require execute rights, a feature that is not available by default. Some configurations where user management has been centralized, but are limited to those 3 roles, make all web applications run under db owner rights so stored procedures can work. Naturally,
Defense Option 3: Whitelist Input Validation
Several parts of SQL queries are not legal locations for the use of bind variables, such as table or column names, and the sort order indicator (ASC or DESC). In such situations, input validation or query redesign is the most appropriate defense. For table or column names, ideally those values come from the code, and not from user parameters.
But if the user parameter values are used to differ from the table and column names, then the parameter values should be mapped to the legal/expected column or column names to ensure that non-validated user input does not end in query. Note that this is a symptom of poor design and a rewrite should be considered if time permits.
Defense option 4: Escaping all user-supplied input
This technique should only be used as a last resort, when none of the above is feasible. Input validation is probably a better option as this methodology is brittle compared to other defenses and we cannot guarantee that it will prevent all SQL injection in all situations.
This technique is for escaping user input before putting it into a query. It is a very specific database in its implementation. Updating legacy code is generally only recommended when implementing input validation is not cost-effective. Applications built from scratch, or applications that require a low tolerance for risk must be built or rewritten using parameterized queries, stored procedures, or some kind of Object Relational Mapper (ORM) that builds your queries for you.
This technique works like this. Each DBMS supports one or more specific character escaping schemes for certain types of queries. If you then escape all user-supplied input using the appropriate escaping scheme for the database you are using, the DBMS will not confuse that input with SQL code written by the developer, thus avoiding potential SQL injection vulnerabilities.
additional defenses
In addition to adopting one of the four main defenses, we also recommend that you adopt all of these additional defenses to provide defense in depth. These additional defenses are:
Minimum privileges
To minimize the potential damage from a successful SQL injection attack, you should minimize the privileges assigned to each database account in your environment. Do not assign DBA or administrator type access rights to your application accounts. We understand that this is easy and everything just "works" when you do it this way, but it is very dangerous.
Start from scratch to determine what access rights your application accounts require, rather than trying to figure out what access rights you need to remove. Make sure that accounts that only need read access only have read access to the tables that they need access to.
If an account only needs to access parts of a table, consider creating a view that limits access to that part of the data and assigns the account's access to the view instead of the underlying table. Rarely, if ever, does it grant create or delete access to database accounts.
If you adopt a policy where you use stored procedures everywhere and don't allow application accounts to directly run their own queries, then restrict those accounts to only running the stored procedures they need. Do not grant any rights directly to the tables in the database.
SQL injection is not the only threat to your database data. Attackers can simply change parameter values from one of the legal values they are presented with, to a value that is not authorized for them, but the application itself might be authorized to access. As such, minimizing the privileges granted to your application will reduce the likelihood of such unauthorized access attempts, even when an attacker is not trying to use SQL injection as part of their vulnerability.
While you're at it, you should minimize the privileges of the operating system account under which the DBMS is running. Do not run your DBMS as root or system! Most DBMSs are exhausted with a very powerful system account. For example, MySQL runs as system on Windows by default! Change the DBMS OS account to something more appropriate, with restricted privileges.
Multiple database users
The web application designer should not only avoid using the same owner/administrator account in the web applications to connect to the database. Different DB users could be used for different web applications.
In general, each separate web application that requires access to the database could have a designated database user account that the web application will use to connect to the database. That way the app designer can have a good granularity in access control, reducing privileges as much as possible. Each database user will have select access to what they need only and write access as needed.
As an example, a login page requires read access to the username and password fields of a table, but does not have write access in any way (no insert, update, or delete). However, the record page certainly requires an insert privilege on that table; this restriction can only be applied if these web applications use different database users to connect to the database.
Views
You can use SQL views to further increase the granularity of access by limiting read access to specific fields in a table or joins of tables. It could potentially have additional benefits: for example, suppose the system is required (perhaps due to some specific legal requirements) to store user passwords, rather than salted passwords.
The designer could use views to compensate for this limitation; revoke all access to the table (from all database users except the owner/admin) and create a view that hashes the password field and not the field itself. Any SQL injection attack that manages to steal information from the database will be limited to stealing the hash of the passwords (it could even be a hash key), since no database user for any of the web applications has access to it. the table itself.
Whitelist input validation
As well as being a primary defense when nothing else is possible (for example, when a bind variable is not legal), input validation can also be a secondary defense used to detect unauthorized input before proceeding to the SQL query. For more information, see the input validation cheat sheet. Proceed with caution here. Validated data is not necessarily safe to insert into SQL queries via string construction.
Short answer? No , it doesn't exist. Long answer? Nothing is 100% safe; Using prepared statements is almost guaranteed to avoid sql injections, however manuals like CheatSheetSeries list several more "main" defenses and a couple of additional defenses, showing that prepared statements are not always enough.
There is a discussion on this topic at https://security.stackexchange.com/questions/15214/are-prepared-statements-100-safe-against-sql-injection that gives some examples with specific cases that allow attacks of this type.