SQL Injection

Last updated on August 10, 2023

In database management systems (DBMS), a prepared statement, parameterized statement, or parameterized query is a feature where the database pre-compiles SQL code and stores the results, separating it from data. Benefits of prepared statements are:

1. efficiency, because they can be used repeatedly without re-compiling
2. security, by reducing or eliminating SQL injection attacks

A common workflow for prepared statements is:

1. Prepare: The application creates the statement template and sends it to the DBMS. Certain values are left unspecified, called parameters, placeholders or bind variables (labelled “?” below): INSERT INTO products (name, price) VALUES (?, ?);
2. Compile: The DBMS compiles (parses, optimizes and translates) the statement template, and stores the result without executing it.
3. Execute: The application supplies (or binds) values for the parameters of the statement template, and the DBMS executes the statement (possibly returning a result). The application may request the DBMS to execute the statement many times with different values. In the above example, the application might supply the values “bike” for the first parameter and “10900” for the second parameter, and then later the values “shoes” and “7400”.

Major DBMSs, including SQLite, MySQL, Oracle, IBM Db2, Microsoft SQL Server and PostgreSQL support prepared statements. Prepared statements are normally executed through a non-SQL binary protocol for efficiency and protection from SQL injection, but with some DBMSs such as MySQL prepared statements are also available using a SQL syntax for debugging purposes

security database security

This is a sapling 🌱 in my digital garden 🏡.

Notes mentioning this note

Database Security

SQL Injection - can be prevented by prepared statement [[sql injection]]