The core of preventing SQL injection is to use parameterized queries to avoid splicing SQL statements; even if ORM is used, you need to be vigilant about splicing risks in native queries; at the same time, you should combine various measures such as input verification, permission minimization and error information processing. 1. Always use parameterized queries, such as cursor.execute() with parameter form; 2. Avoid splicing variables in raw() and other methods in ORM; 3. Whitelist verification of inputs; 4. Minimum permissions for database accounts; 5. Turn off unnecessary database functions; 6. Do not expose detailed error information to users.
It is a disaster to not be able to prevent SQL injection when writing code. Especially when using Python for back-end development, the database operation is frequent, and it is easy to get stuck if you don’t pay attention. To truly prevent SQL injection, filtering input alone is far from enough, you have to start with the writing method and overall structure.

Use parameterized query, don't spell SQL statements
The most core point is: never splice SQL statements yourself . Many people are accustomed to using string formatting to splice variables into SQL, such as:
query = f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'"
This is very dangerous to write. As long as someone enters something like ' OR '1'='1
, the verification can be bypassed. The correct way to do this is to use parameterized queries:

cursor.execute("SELECT * FROM users WHERE username = %s AND password = %s", (username, password))
The database driver automatically processes parameters to ensure that the input is not executed as SQL. This method is supported in mainstream libraries such as psycopg2
(PostgreSQL), MySQLdb
, and sqlite3
.
Be vigilant about ORM
Many people think that using ORM (such as SQLAlchemy or Django ORM) is foolproof, but it is not. ORM can indeed help you avoid most injection problems, but if you use native SQL queries or splicing statements, there are still risks.

For example, writing like this in Django is dangerous:
User.objects.raw(f"SELECT * FROM myapp_user WHERE id = {user_id}")
Although the raw()
method of ORM is used, it is still easy to be injected after splicing variables. The correct way is to pass parameters in:
User.objects.raw("SELECT * FROM myapp_user WHERE id = %s", [user_id])
Therefore, even if you use ORM, you must be careful not to mess with SQL , otherwise something will happen.
It is also important to enter verification and restrict permissions
In addition to writing, risks must also be controlled from the source. for example:
- Verify the input whitelist, for example, only alphanumeric usernames are allowed, and the mailbox format must be compliant.
- Minimize database account permissions, do not use
root
oradmin
account for web applications - Unnecessary database functions are turned off, such as stored procedures, remote access, etc.
Although these measures cannot completely replace parameterized queries, they can greatly reduce the possibility of successful attacks.
In addition, the error message should not be directly exposed to the user. for example:
except Exception as e: print(f"Database error: {e}") # Don't return to the front end like this
The attacker can reversely deduce the database structure through error messages. It is recommended to return "system error" uniformly and record the detailed information in the log.
Basically that's it. SQL injection prevention seems simple, but if you really want to write well, you have to start from multiple aspects of writing, framework usage, and permission control. It is impossible to defend against just one method. The key is to form a complete set of defensive habits.
The above is the detailed content of Securing Python Code Against SQL Injection Attacks. For more information, please follow other related articles on the PHP Chinese website!

Hot AI Tools

Undress AI Tool
Undress images for free

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Polymorphism is a core concept in Python object-oriented programming, referring to "one interface, multiple implementations", allowing for unified processing of different types of objects. 1. Polymorphism is implemented through method rewriting. Subclasses can redefine parent class methods. For example, the spoke() method of Animal class has different implementations in Dog and Cat subclasses. 2. The practical uses of polymorphism include simplifying the code structure and enhancing scalability, such as calling the draw() method uniformly in the graphical drawing program, or handling the common behavior of different characters in game development. 3. Python implementation polymorphism needs to satisfy: the parent class defines a method, and the child class overrides the method, but does not require inheritance of the same parent class. As long as the object implements the same method, this is called the "duck type". 4. Things to note include the maintenance

Parameters are placeholders when defining a function, while arguments are specific values ??passed in when calling. 1. Position parameters need to be passed in order, and incorrect order will lead to errors in the result; 2. Keyword parameters are specified by parameter names, which can change the order and improve readability; 3. Default parameter values ??are assigned when defined to avoid duplicate code, but variable objects should be avoided as default values; 4. args and *kwargs can handle uncertain number of parameters and are suitable for general interfaces or decorators, but should be used with caution to maintain readability.

Iterators are objects that implement __iter__() and __next__() methods. The generator is a simplified version of iterators, which automatically implement these methods through the yield keyword. 1. The iterator returns an element every time he calls next() and throws a StopIteration exception when there are no more elements. 2. The generator uses function definition to generate data on demand, saving memory and supporting infinite sequences. 3. Use iterators when processing existing sets, use a generator when dynamically generating big data or lazy evaluation, such as loading line by line when reading large files. Note: Iterable objects such as lists are not iterators. They need to be recreated after the iterator reaches its end, and the generator can only traverse it once.

A class method is a method defined in Python through the @classmethod decorator. Its first parameter is the class itself (cls), which is used to access or modify the class state. It can be called through a class or instance, which affects the entire class rather than a specific instance; for example, in the Person class, the show_count() method counts the number of objects created; when defining a class method, you need to use the @classmethod decorator and name the first parameter cls, such as the change_var(new_value) method to modify class variables; the class method is different from the instance method (self parameter) and static method (no automatic parameters), and is suitable for factory methods, alternative constructors, and management of class variables. Common uses include:

The key to dealing with API authentication is to understand and use the authentication method correctly. 1. APIKey is the simplest authentication method, usually placed in the request header or URL parameters; 2. BasicAuth uses username and password for Base64 encoding transmission, which is suitable for internal systems; 3. OAuth2 needs to obtain the token first through client_id and client_secret, and then bring the BearerToken in the request header; 4. In order to deal with the token expiration, the token management class can be encapsulated and automatically refreshed the token; in short, selecting the appropriate method according to the document and safely storing the key information is the key.

Python's magicmethods (or dunder methods) are special methods used to define the behavior of objects, which start and end with a double underscore. 1. They enable objects to respond to built-in operations, such as addition, comparison, string representation, etc.; 2. Common use cases include object initialization and representation (__init__, __repr__, __str__), arithmetic operations (__add__, __sub__, __mul__) and comparison operations (__eq__, ___lt__); 3. When using it, make sure that their behavior meets expectations. For example, __repr__ should return expressions of refactorable objects, and arithmetic methods should return new instances; 4. Overuse or confusing things should be avoided.

Python's garbage collection mechanism automatically manages memory through reference counting and periodic garbage collection. Its core method is reference counting, which immediately releases memory when the number of references of an object is zero; but it cannot handle circular references, so a garbage collection module (gc) is introduced to detect and clean the loop. Garbage collection is usually triggered when the reference count decreases during program operation, the allocation and release difference exceeds the threshold, or when gc.collect() is called manually. Users can turn off automatic recycling through gc.disable(), manually execute gc.collect(), and adjust thresholds to achieve control through gc.set_threshold(). Not all objects participate in loop recycling. If objects that do not contain references are processed by reference counting, it is built-in

Pythonmanagesmemoryautomaticallyusingreferencecountingandagarbagecollector.Referencecountingtrackshowmanyvariablesrefertoanobject,andwhenthecountreacheszero,thememoryisfreed.However,itcannothandlecircularreferences,wheretwoobjectsrefertoeachotherbuta
