SQL Injection fundamentals

SQL injection becomes much easier to understand when the underlying SQL syntax is clear. A web application usually builds queries to read or modify data. If user-controlled input is inserted into those queries without proper parameterisation, that input may change the structure or logic of the SQL statement.

The MySQL client can connect directly to a database server using a username, host and port. The -p option asks for the password interactively.

A MySQL server can contain multiple databases. SHOW DATABASES lists them, while USE changes the active database for subsequent queries.

Once a database is selected, SHOW TABLES reveals the tables available in that database. DESCRIBE shows the structure of a table, including its columns and data types.

SELECT retrieves data from a table. The asterisk returns every column, while specifying column names limits the output to the fields that are actually needed.

WHERE restricts the rows returned by a query. LIKE performs pattern matching, and the percent character acts as a wildcard in MySQL.

ORDER BY controls the order of returned rows. ASC sorts ascending and DESC descending. LIMIT restricts how many rows MySQL returns and can also specify an offset.

SQL conditions are evaluated using operators such as =, !=, LIKE, NOT, AND and OR. Understanding operator precedence is important because an injected OR condition can change whether the WHERE clause evaluates to true or false.

Consider an application that builds a query by directly concatenating a username supplied by the user. If that value contains SQL syntax, the resulting statement may no longer represent the query the developer intended. SQL injection is therefore not simply inserting strange characters: it is changing the structure or logic of the database query.

One of the first signs of SQL injection can appear when a quote changes the application response or produces a database error. Quotes matter because applications frequently place user input inside SQL string literals.

If user input becomes part of an authentication query, a boolean expression that always evaluates to true may alter the WHERE condition. SQL comments can also remove the remainder of the original query. The exact payload depends on the surrounding SQL syntax.

After injecting valid SQL syntax, the rest of the application query may still contain characters that cause an error. MySQL comments can cause the remaining portion of the original query to be ignored. The -- comment syntax normally requires whitespace after the two hyphens, which is why examples often use -- -.

UNION combines the result of one SELECT statement with another. For UNION injection to work, both SELECT statements must return a compatible number of columns, and corresponding values need compatible data types.

Before constructing a UNION query, determine how many columns the original SELECT returns. ORDER BY can be incremented until the application produces an error, indicating that the requested column position no longer exists.

Another technique is to submit numbered values through UNION SELECT. If the number of supplied values does not match the original query, MySQL returns an error. Once the column count matches, the UNION query can execute.

Numbered UNION values can also reveal which columns are rendered back into the HTTP response. If the page displays one of the numbers, that position can later be replaced with database information.

@@version returns the MySQL server version when query results are visible. SLEEP() can produce a controlled delay and is useful for understanding situations where the application does not directly display database output.

The database() function returns the database currently selected by the application connection.

MySQL exposes metadata through INFORMATION_SCHEMA. Instead of guessing database, table and column names, this metadata can describe the structure of the server itself.

INFORMATION_SCHEMA.SCHEMATA contains database names. The schema_name column can therefore be queried to list databases visible to the current MySQL account.

INFORMATION_SCHEMA.TABLES contains table metadata. Filtering by table_schema limits the output to a specific database.

After identifying an interesting table, INFORMATION_SCHEMA.COLUMNS can reveal its column names. This provides the structure needed before querying the table itself.

MySQL supports fully qualified table names in the form database.table. Once the database, table and columns are known, a query can reference them directly.

The user() function shows the account associated with the current database connection. This is important because the account permissions determine what the application can access.

The impact of SQL injection depends heavily on database permissions. INFORMATION_SCHEMA.USER_PRIVILEGES can show privileges available to database accounts, while mysql.user may expose administrative attributes when the current user has permission to read it.

MySQL can restrict file import and export operations through secure_file_priv. Depending on its configuration and the database account permissions, file operations may be restricted to one directory or disabled.

MySQL LOAD_FILE() can read a file that the database process can access when the account has the necessary privileges and the server configuration allows it. This is why database accounts should have the minimum privileges required by the application.

SELECT ... INTO OUTFILE can create a file on the database server when MySQL permissions, operating-system permissions and secure_file_priv allow it. This significantly increases the impact of a SQL injection vulnerability and should normally be prevented by least-privilege database configuration.

A structured SQL injection assessment is easier to understand than trying random payloads. First determine whether input affects SQL syntax, then identify the query structure, determine the column count, locate visible output, fingerprint the DBMS and enumerate metadata only as far as necessary for the lab objective.

The primary defence is to keep user data separate from SQL syntax by using parameterised queries or prepared statements. Applications should also validate input, avoid exposing detailed database errors, run with a least-privilege database account and avoid granting unnecessary FILE or administrative privileges.

SQL injection is fundamentally a query-construction problem. Learning SQL syntax, boolean logic and database metadata makes the vulnerability much easier to understand. UNION injection is only one technique; the real objective is to determine how user input influences the query and what privileges the application database account actually has.