What does SQL Injection involve?

SQL injection involves taking advantage of insecure handling of user input in a web application's database queries. When user input is not properly validated and sanitized, it can be exploited by attackers to manipulate the structure and behavior of the database queries.

For example, let's consider a form on a website that takes user input for a search query and directly concatenates it with an SQL query. If the input is not properly sanitized or validated, an attacker can input SQL commands that alter the original query's behavior. This could allow the attacker to retrieve, modify, or delete sensitive data from the database.

To prevent SQL injection, as a programmer, it's important to use parameterized queries or prepared statements. These techniques allow the separation of SQL logic from the actual user input, preventing malicious SQL commands from altering the query structure. Additionally, input validation should be enforced at both the client and server sides to ensure that only expected and safe input is accepted.

Furthermore, using stored procedures, which are precompiled SQL queries stored in the database, can also help prevent SQL injection. These procedures can restrict the execution of arbitrary SQL code and provide an additional layer of defense against such attacks.

Here are a couple of examples to help illustrate the concept of SQL injection and how it can be exploited:

Example 1:
Suppose you have a website with a search functionality that allows users to search for products by entering keywords. The website uses an SQL query to retrieve the matching products from the database. The query might look like this:

SELECT * FROM products WHERE name = '<user_input>';


If the website takes the user input directly and appends it to the SQL query without proper validation, an attacker could input something like this into the search box:

' OR 1=1 --


The resulting query would become:

SELECT * FROM products WHERE name = '' OR 1=1 --';


In this case, the injected SQL code after the user input causes the query to return all products from the database, as the condition "1=1" is always true. The double hyphens " -- " denote a comment in SQL, effectively nullifying the rest of the original query.

Example 2:
Consider a login form on a website where the user provides a username and password to authenticate. The website uses an SQL query to check the credentials in the database, as follows:

SELECT * FROM users WHERE username = '<provided_username>' AND password = '<provided_password>';


If the website simply appends the provided username and password directly without proper sanitation, an attacker could input the following into the username field:

' OR '1'='1


The resulting query would be:

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '<provided_password>';


In this case, the injected SQL code allows the attacker to bypass the authentication check, as the condition '1'='1' is always true, resulting in the query returning the first user without requiring a correct password.

These examples demonstrate how SQL injection can be used to manipulate database queries through unsanitized user input, potentially leading to unauthorized access and data leakage. It's crucial for programmers to implement proper input validation and parameterized queries to mitigate the risk of such attacks.

SQL is a super cool language used for managing databases. When you write queries in SQL, it looks something like this:

"SELECT * FROM users WHERE name = 'Google';"

Basically, this query would find all the users in the users table with the name 'Google'. Sometimes, in the code, it might look like this in Python:

"return "SELECT * FROM users WHERE name = '%s';" % name"

This means that the query is put together as a string, and the value of the name variable gets inserted into the query in place of %s.

But here's the thing – when queries are built in this way, it can be pretty risky because it opens the door to SQL injections. This is when sneaky variables are used to change the query in a significant way, doing things you didn't intend.

For example, there was this person in Poland who came up with a company name like "Dariusz Jakubowski x'; DROP TABLE users; SELECT '1". If this company filled out a form with this name and Python code created an SQL query like this:

"SELECT * FROM users WHERE name = 'Dariusz Jakubowski x'; DROP TABLE users; SELECT '1';"

Instead of finding users with the name 'Dariusz Jakubowski x', the code would actually delete the whole users table (!!!) and then return '1'.

So, with SQL injections, attackers can mess with queries and mess up how the database works, even deleting some important data.

To fight against this, techniques like escaping are used to make sure the same quotation marks aren't used in the name as in the query. It's kind of like putting on a safeguard to protect against sneaky tricks. And that's how we deal with SQL injections.

Designing a fully-operational website typically requires the utilization of a database server. In most cases, a MySQL database server is used due to its reliability, stability, and speed, depending on the nature of the operation being performed, such as inserting, fetching, deleting, or modifying data. Common issues often revolve around handling and processing requests, which if done improperly, can lead to security breaches.

How does SQL injection facilitate hacking? Data input for requests often comes from users, such as when filling out a form or individual fields (using the POST method), or via a generated and displayed text in the URL bar from a GET request. For instance, consider a link to an article page: site.com/article.php?id=1. This URL includes the identifier "id" and its associated value. Depending on this value, the page displays corresponding content queried from the database using that identifier. If an attacker manipulates the value from, for example, 1 to "1 UNION SELECT * FROM admin" in the URL, the subsequent query could potentially expose data from an admin table, granting unauthorized access.

In the aforementioned scenario, the UNION operator was exploited to merge the intended query with a separate, malicious query. Furthermore, techniques such as manipulating string parameters can also be utilized. For instance, the initial query "SELECT *FROM articles WHERE title LIKE ('%$title%')" can be tampered with by inserting additional words like "'and id = '1", thus altering the query's logic and potentially compromising security.