September 2024
After months of hard work, you've created your first webshop. On the landing page, you've set up a search bar that helps users find what they're looking for.
The attacker finds your site and enters some text into the search box, performing their attack.
All of a sudden, the data is gone 😮! People are no longer able to find products, with the error indicating that the products table no longer exists.
Error: database table `products` not found
The webshop landing page is a simple HTML website. The search request is handled by a Node.JS Express backend (written in JavaScript), which we'll get to in a couple of steps.
1<!--
2Your brand here webshop
3-->
4<html>
5 <head>
6 <title>Webshop</title>
7 </head>
8 <body>
9 <h1>Your brand here</h1>
10 <form method="post" action="/search">
11 <input type="text" name="search-term" placeholder="Search...">
12 </form>
13 </body>
14</html>
When a user enters a search term, the browser makes a HTTP Post request to the /search endpoint with a `search-term` parameter in the body of the request.
1<!--
2Your brand here webshop
3-->
4<html>
5 <head>
6 <title>Webshop</title>
7 </head>
8 <body>
9 <h1>Your brand here</h1>
10 <form method="post" action="/search">
11 <input type="text" name="search-term" placeholder="Search...">
12 </form>
13 </body>
14</html>
The /search endpoint makes a database request to fetch matching products.
1//
2// Your brand here webshop
3//
...
10app.post('/search', function(request, result) {
11 const searchTerm = request.body['search-term'];
12 con.query(
13 "SELECT * FROM products WHERE name like '%" + searchTerm + "%'",
14 function (err, rows, fields) {
... ...
20 })
... ...
30})
When the user makes a search request, the searchTerm variable contains their search query.
1//
2// Your brand here webshop
3//
...
10app.post('/search', function(request, result) {
11 const searchTerm = request.body['search-term'];
12 con.query(
13 "SELECT * FROM products WHERE name like '%" + searchTerm + "%'",
14 function (err, rows, fields) {
... ...
20 })
... ...
30})
In the next line, the same search term is included in the database query.
1//
2// Your brand here webshop
3//
...
10app.post('/search', function(request, result) {
11 const searchTerm = request.body['search-term'];
12 con.query(
13 "SELECT * FROM products WHERE name like '%" + searchTerm + "%'",
14 function (err, rows, fields) {
... ...
20 })
... ...
30})
What if the attacker types `'; drop table products; --` into the search box?
1//
2// Your brand here webshop
3//
...
10app.post('/search', function(request, result) {
11 const searchTerm = request.body['search-term'];
12 con.query(
13 "SELECT * FROM products WHERE name like '%" + "'; drop table products; --" + "%'",
14 function (err, rows, fields) {
... ...
20 })
... ...
30})
The database executes 2 query statements, dropping the `product` table in the process, resulting in a loss of data.
1//
2// Your brand here webshop
3//
...
10app.post('/search', function(request, result) {
11 const searchTerm = request.body['search-term'];
12 con.query(
13 "SELECT * FROM products WHERE name like '%'; drop table products; --%'",
14 function (err, rows, fields) {
... ...
20 })
... ...
30})
1<!--
2Your brand here webshop
3-->
4<html>
5 <head>
6 <title>Webshop</title>
7 </head>
8 <body>
9 <h1>Your brand here</h1>
10 <form method="post" action="/search">
11 <input type="text" name="search-term" placeholder="Search...">
12 </form>
13 </body>
14</html>
The preferred way to prevent SQL injection is to use a safe API with prepared statements. This way, the app is not manually constructing queries with string concatenation, but delegating the task to well-tested libraries.
Note: Using prepared statements with stored procedures isn't a silver bullet. PL/SQL or T-SQL code within them can still cause SQL injection if it concatenates user input with queries or uses EXECUTE IMMEDIATE/exec() on untrusted data.
1//
2// Your brand here webshop
3//
...
10app.post('/search', function(request, result) {
11 const searchTerm = request.body['search-term'];
12 const sql = "SELECT * FROM products WHERE name like ?";
13 con.query(sql, [`%${searchTerm}%`], function (err, rows, fields) {
... ...
20 })
... ...
30})
Restrict input to expected characters and perform this check on the server.
Note: Positive input validation is a limited defense when applications expect special characters in user input (as in the example from the previous section — "Plug'N'Play Video Game Console" could be a valid search term).
1//
2// Your brand here webshop
3//
...
10app.post('/search', function(request, result) {
11 const searchTerm = request.body['search-term'];
12 const validateLetters = text => /^[A-Za-z]+$/.test(text);
13 if (!validateLetters(searchTerm)) return;
14 const sql = "SELECT * FROM products WHERE name like ?";
15 con.query(sql, [`%${searchTerm}%`], function (err, rows, fields) {
... ...
20 })
... ...
30})
Use a safe API to escape special characters from input.
1//
2// Your brand here webshop
3//
...
10app.post('/search', function(request, result) {
11 const searchTerm = con.escape(request.body['search-term']);
12 const sql = "SELECT * FROM products WHERE name like ?";
13 con.query(sql, [`%${searchTerm}%`], function (err, rows, fields) {
... ...
20 })
... ...
30})
Try to limit the extent of the impact in case a SQL injection attack still happens.
1//
2// Your brand here webshop
3//
...
10app.post('/search', function(request, result) {
11 const searchTerm = request.body['search-term'];
12 const sql = "SELECT * FROM products WHERE name like ?";
13 con.query(sql, [`%${searchTerm}%`], function (err, rows, fields) {
... ...
19 result.send(rows.slice(0, 10));
20 })
... ...
30})
© 2025 Analitiq