Essential Protocols for Python Developers to Prevent SQL Injection Attacks

You are going to encounter a number of issues as a Python developer. Mastering the syntax of coding isn’t enough to write functioning, stable applications. You also have to familiarize yourself with different challenges the final application might deal with, including Python security risks.

Many of the discussions about developing secure applications focus on using machine learning to protect customers, such as helping them avoid holiday scams. However, it is equally important to ensure the applications themselves are not vulnerable to cybercriminals.

One of the challenges that Python developers must cope with is guarding their applications against cyberattacks. One of the biggest security problems that haunt software developers are SQL injections. Bitninja reports that SQL injections account for over 50% of all application attacks. This technique can allow SQL code to run through the client application without any restrictions, and thus silently alter data. The system administrators often don’t notice these changes until it is too late.

This is a very serious security risk that can have daunting consequences if it is not prevented. Therefore, it is important to understand the risks of SQL injection and steps that should be taken to protect Python applications from these types of attacks.

What Are SQL Injections and What Threat Do They Pose to Python Applications?

As mentioned earlier, SQL injections allow SQL code to be executed from the client application directly into the database. These attacks allow hackers to change data unrestrictedly and without consent of the system administrators. This is a very serious problem that can seriously compromise the security of a system if they are not thwarted in time.

SQL attacks can have a wide range of consequences for companies, such as:

• Website damage: An attacker can delete or modify a company's database and consequently destroy the website.
• Data theft or leakage: Many attacks aim to steal confidential data such as trade secrets, sensitive information, intellectual property, and — more often — information about the company's users or customers. This information can then be sold to competitors to gain commercial advantage.
• Privilege escalation: An attacker could use the contents of a breached database to gain access to other parts of a company's internal network.
• Loss of reputation and trustworthiness: It is often difficult for a company to regain the trust of its customers after a cyberattack.

An analysis by the Open Web Application Security Project shows that there were over 274,000 SQL injection attacks on applications in 2021. That figure is likely growing each year.

To better understand this problem, let's take a practical example. Imagine a simple application that updates customers by passing their name and age. Focusing only on the back end of the application and without any SQL injection checking, the code responsible for updating clients would basically be as follows:

name = 'John' cursor.execute(f "UPDATE customer SET name={name} WHERE idcustomer=13")

The above code will update the name of the customer with id 13 to "John". It appears to work effectively so far. However, it has some serious security risks bubbling under the surface.

At first glance, it seems that we are just updating the name of a customer in our database. However, imagine that instead of passing just 'John' to the name variable, we pass some SQL code:

name = "'Carlos' , age = 80" cursor.execute(f "UPDATE customer SET name={name} WHERE idclient=13")

The above code will allow the name and age of the client with an id of “13” to be changed simultaneously, without permission or consent of the system administrator. It may seem silly not to allow the age of a customer to be edited, but imagine a banking system with this same problem and allowing the balance value to be changed by the user.

This is a complex situation, which can have untold consequences if it is not remedied. But what steps can be taken to resolve them?

What Can Python Developers Do to Prevent SQL Injection Attacks?

To solve the SQL injection problem, we need to parameterize the queries used in our program. We need to make sure that we do not allow SQL code to be executed on the client side of the application. To do this, we alter the query as follows:

name = "'Carlos' , age = 80" cursor.execute("UPDATE client SET name=%(name)s WHERE idclient=13", ({'name': name, }))

With the above code, we will no longer execute the code present in the "name" variable in the UPDATE. Instead, the entire contents of this variable will be stored as the name of the customer with id 13. Therefore, the name of the customer with id 13 will be "Carlos, age = 80" and his age will remain unchanged.

Conclusion

This way, we will no longer allow the fields of a particular table to be changed without system permission, and thus ensure much more security for our application.