php – SQL injection in prepared queries

Question:

The importance of protecting our websites against SQL injection attacks is highly recommended to keep our data safe. Good practice recommends using prepared queries to avoid these attacks. But this is where I go, is there or do you know an 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 question that I have for a future web security project, where several attacks will be applied to test the vulnerability of the site.

Greetings.

Answer:

As I 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 from OWASP, an authority on security in general.

In SQL Injection Prevention Cheat Sheet they consider a primary defense level and an additional defense level (in the link there are examples of safe code according to the languages ​​/ drivers) and at the end a series of links if you want to go deeper into the subject) :

Primary defenses:

  • Option 1: Using prepared statements (with parameterized queries)
  • Option 2: Use stored procedures
  • Option 3: Whitelist input validation
  • Option 4: Escape all user supplied inputs

Additional defenses:

  • Also: Enforce least privilege
  • Also: Performing whitelist input validation as a secondary defense

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 the user input that 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 tom 'o' 1 '=' 1 userid tom 'o' 1 '=' 1 , the parameterized query would not be vulnerable and would instead search for a username that literally matches the string tom 'o' 1 '=' 1 .

Specific language recommendations:

  • Java EE – use PreparedStatement () with binding variables
  • .NET: use parameterized queries like SqlCommand () or OleDbCommand () with binding variables
  • PHP: use PDO with strongly typed parameterized queries (using bindParam ())
  • Hibernate: use createQuery () with binding variables (called named parameters in Hibernate)
  • SQLite: use sqlite3_prepare () to create declaration object

In rare circumstances, prepared statements can hurt performance. When faced with this situation it is better to: a) vigorously validate all data or b) remove all user supplied input using an escape routine specific to your database provider as described below, rather than using a prepared statement.

Defense Option 2: Stored Procedures

Stored procedures are not always safe against 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 parameterized automatically 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 at 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 SQL generation. Developers do not typically generate dynamic SQL within stored procedures. However, it can be done, but it should be avoided. If it cannot be avoided, the stored procedure should use appropriate input validation or escape as described in this article to ensure that all user-supplied input to the stored procedure cannot be used to inject SQL code into the dynamically generated query. Auditors should always look for uses of sp_execute, execute, or exec within SQL Server stored procedures. Similar audit guides are required for similar functions for other vendors.

There are also several cases where stored procedures can increase risk. For example, on 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 rights to the web service user, depending on the requirements. However, stored procedures require execute rights, a function that is not available by default. Some configurations where user management has been centralized, but limited to those 3 roles, make all web applications run under db owner rights so stored procedures can work. Naturally, that means that if a server is breached, the attacker has full rights to the database, where previously they could only have read access.

Defense Option 3: Whitelist Entry Validation

Various parts of SQL queries are not legal locations for the use of binding 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 code, not user parameters.

But if user parameter values ​​are used to differentiate from table and column names, then parameter values ​​should map to legal / expected column or column names to ensure that unvalidated user input does not finish in consultation. Note that this is a symptom of poor design and rewriting should be considered if time permits.

Defense Option 4: Escape All User Provided Inputs

This technique should only be used as a last resort, when neither of the above is feasible. Input validation is probably a better option as this methodology is fragile compared to other defenses and we cannot guarantee that it will prevent all SQL injection in all situations.

This technique is to escape user input before putting it into a query. It is a very specific database in its implementation. Generally, updating legacy code is only recommended when implementing input validation is not cost effective. Applications built from scratch, or applications that require a low risk tolerance should 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 escape schemes for certain types of queries. If you then escape all user-supplied input using the appropriate escape scheme for the database you are using, the DBMS will not confuse that input with developer-written SQL code, 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 a defense in depth. These additional defenses are:

  • Minimum privileges
  • Whitelist input validation

Minimum privileges

To minimize the potential damage from a successful SQL injection attack, you must minimize the privileges assigned to each database account in your environment. Do not assign DBA or administrator access rights to your application accounts. We understand that this is easy and that everything just "works" when you do it this way, but it is very dangerous.

Start from scratch to determine what access rights your app accounts require, rather than trying to figure out what access rights you need to remove. Make sure accounts that only need read access have read only 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 access to the view rather than the underlying table. Rarely, if ever, 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 so that they can only run the stored procedures they need. Do not grant them any rights directly to the tables in the database.

SQL injection is not the only threat to your database data. Attackers can simply change the 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 could 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. Don't run your DBMS as root or system! Most DBMS are depleted with a very powerful system account. For example, MySQL runs as Windows system by default! Change the DBMS operating system 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 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 application designer can have 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 (do not insert, update, or delete). However, the registration 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 table joins. 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 passwords with salt.

The designer could use views to compensate for this limitation; revoke all access to the table (from all database users except owner / administrator) and create a view that generates the hash of the password field and not the field itself. Any SQL injection attack that succeeds in stealing information from the database will simply steal the password hash (it could even be a hash key), as no database user for any of the web applications has access to it. the table itself.

Whitelist input validation

In addition to being a primary defense when nothing else is possible (for example, when a binding variable is not legal), input validation can also be a secondary defense used to detect unauthorized input before moving on to the SQL query. For more information, see the input validation reference sheet. Proceed with caution here. Validated data is not necessarily safe to insert into SQL queries through string construction.

Scroll to Top