Enum Databases

Theory

When exploiting SQL injection vulnerabilities, or when you gain access to the database itself, it is often necessary to gather some information about the database itself. This includes the type and version of the database software, and the contents of the database in terms of which tables and columns it contains or even users and permissions informations.

Practice

Some queries on this page can be used with different SQLi techniques as UNION or Blind based attacks

Database version

Different databases provide different ways of querying their version. You often need to try out different queries to find one that works, allowing you to determine both the type and version of the database software. The queries to determine the database version for some popular database types are as follows:

SELECT @@version

Database Names

When performing SQL injections, it can be useful to know the names of the databases that are present on the targeted server. Enumerating the database names allows you to identify which databases are available and potentially gain insight into the server's configuration and architecture. This information can be used to craft more targeted and effective SQL injection attacks.

We can enum the current database with the following query:

SELECT database();

We can list all databases with the following query:

SELECT schema_name FROM information_schema.schemata;

We can enum the current database with the following query:

SELECT DB_NAME();

We can list all databases with the following queries:

SELECT name FROM master..sysdatabases;
#Or
SELECT DB_NAME(N); — for N = 0, 1, 2, …

#Or in mssqlclient's impacket shell
enum_db

We can enum the current database with the following queries:

SELECT global_name FROM global_name;
SELECT name FROM V$DATABASE;
SELECT instance_name FROM V$INSTANCE;
SELECT SYS.DATABASE_NAME FROM DUAL;

We can list all databases with the following query:

SELECT DISTINCT owner FROM all_tables;

We can extract current database structure with the following query:

SELECT sql FROM sqlite_schema;

We can list all databases with the following query:

PRAGMA database_list;
SELECT name FROM pragma_database_list;

Tables Names

The next step in performing SQL injections is to enumerate the tables that are present within each database. Enumerating the table names can provide valuable information about the structure and content of the databases.

SELECT table_name FROM information_schema.tables;
SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE();

SELECT name FROM master..sysobjects WHERE xtype = ‘U’; — use xtype = ‘V’ for views
SELECT name FROM someotherdb..sysobjects WHERE xtype = ‘U’;
SELECT master..syscolumns.name, TYPE_NAME(master..syscolumns.xtype) FROM master..syscolumns, master..sysobjects WHERE master..syscolumns.id=master..sysobjects.id AND master..sysobjects.name=’sometable’; — list colum names and types for master..sometable

SELECT table_name FROM information_schema.tables;
SELECT table_name FROM information_schema.tables WHERE table_catalog = DB_NAME();

SELECT table_name FROM all_tables;

SELECT table_name FROM all_tables WHERE owner = USER;
SELECT table_name FROM all_tables WHERE owner = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA');

SELECT owner, table_name FROM all_tables;
SELECT owner, table_name FROM all_tab_columns WHERE column_name LIKE '%PASS%';

Columns Names

Next step is to enumerate columns within tables.It's a crucial step in the process of exploiting a SQL injection vulnerability.

SELECT column_name FROM information_schema.columns WHERE table_name = 'TABLE-NAME-HERE';

DB Users

Additionally, we may enumerate DB users with following queries.

#Get all users
SELECT * FROM mysql.user;

#Get current user
SELECT user();

In MSSQL, logins and users are both types of security principals, but they operate at different scopes.

A login is defined at the server level and is used to authenticate access to the SQL Server instance
An user is defined at the database level and controls access to specific database resources.

A single login can be associated with one user per database, allowing it to access multiple databases under distinct user contexts.

Enumerate Users

#Get all users
select sp.name as login, sp.type_desc as login_type, sl.password_hash, sp.create_date, sp.modify_date, case when sp.is_disabled = 1 then 'Disabled' else 'Enabled' end as status from sys.server_principals sp left join sys.sql_logins sl on sp.principal_id = sl.principal_id where sp.type not in ('G', 'R') order by sp.name;

#Get current user
select user_name();

#Or in mssqlclient's impacket shell
enum_users

Enumerate Logins

# Get all logins
SELECT r.name, r.type_desc, r.is_disabled, sl.sysadmin, sl.securityadmin, sl.serveradmin, sl.setupadmin, sl.processadmin, sl.diskadmin, sl.dbcreator, sl.bulkadmin FROM master.sys.server_principals r LEFT JOIN master.sys.syslogins sl ON sl.sid = r.sid WHERE r.type IN ('S','E','X','U','G');

#Or in mssqlclient's impacket shell
enum_logins

#Get all users
SELECT * FROM pg_catalog.pg_user;
#Or
SELECT usename AS role_name,
 CASE
  WHEN usesuper AND usecreatedb THEN
    CAST('superuser, create database' AS pg_catalog.text)
  WHEN usesuper THEN
    CAST('superuser' AS pg_catalog.text)
  WHEN usecreatedb THEN
    CAST('create database' AS pg_catalog.text)
  ELSE
    CAST('' AS pg_catalog.text)
 END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;
#Or if in a SQL Shell
postgres> \du+

#Get current user
SELECT current_user;

Permissions & Privileges

Sometimes it can be useful to enumerate user's permissions or privileges. We can acheive this with the following queries.

#Show privileges granted to the current MySQL user
mysql> SHOW GRANTS;

#Show privileges granted to a particular MySQL user account from a given host
mysql> SHOW GRANTS FOR 'user_name'@'host';
mysql> SHOW GRANTS FOR 'root'@'localhost';

Introduction about some MSSQL terms:

Securable: These are the resources to which the SQL Server Database Engine authorization system controls access. There are three broader categories under which a securable can be differentiated:
- Server – For example databases, logins, endpoints, availability groups and server roles
- Database – For example database role, application roles, schema, certificate, full text catalog, user
- Schema – For example table, view, procedure, function, synonym
Permission: Every SQL Server securable has associated permissions like ALTER, CONTROL, CREATE that can be granted to a principal. Permissions are managed at the server level using logins and at the database level using users.
Principal: The entity that receives permission to a securable is called a principal. The most common principals are logins and database users. Access to a securable is controlled by granting or denying permissions or by adding logins and users to roles which have access.

# Show all different securables names
SELECT distinct class_desc FROM sys.fn_builtin_permissions(DEFAULT);

# Show all possible permissions in MSSQL
SELECT * FROM sys.fn_builtin_permissions(DEFAULT);

# Get all my permissions over securable type SERVER
SELECT * FROM fn_my_permissions(NULL, 'SERVER');

# Get all my permissions over a database
USE <database>
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');

# Get members of the role "sysadmin"
Use master
EXEC sp_helpsrvrolemember 'sysadmin';

# Get if the current user is sysadmin
SELECT IS_SRVROLEMEMBER('sysadmin');

# Get users that can run xp_cmdshell (except DBA)
Use master
EXEC sp_helprotect 'xp_cmdshell'

# Make user DB Admin (DBA)
EXEC master.dbo.sp_addsrvrolemember 'user', 'sysadmin;

# Get all system privileges granted to all users 
# GRANTEE is the name, role, or user that was assigned the privilege.
# PRIVILEGE is the privilege that is assigned.
# ADMIN_OPTION indicates if the granted privilege also includes the ADMIN option.
SELECT * FROM DBA_SYS_PRIVS;

# Get which users have direct grant access to a table
# GRANTEE is the name, role, or user that was assigned the privilege.
# TABLE_NAME is the name of the object (table, index, sequence, etc).
# PRIVILEGE is the privilege assigned to the GRANTEE for the associated object.
SELECT * FROM DBA_TAB_PRIVS;

#Get current user's privilegs
SELECT * FROM USER_SYS_PRIVS;

Privileges that are inhereted through other roles will not be readily shown. To resolve this, it is advisable to use this advanced script by David Arthur:

14KB

find_all_privs2.sql

Open

#Enumerate users privileges over databases (in a SQL Shell)
postgres> \l

#Enumerate users privileges over tables
SELECT * FROM information_schema.table_privileges;
#in a SQL Shell
postgres> \du+

#Enumerate specific user privileges
SELECT * from information_schema.table_privileges WHERE grantee = 'username';

#Enumerate users privileges over a specific table
SELECT * from information_schema.table_privileges WHERE table_name = 'MyTableName';

Resources

What is SQL Injection? Tutorial & Examples | Web Security AcademyWebSecAcademy

PayloadsAllTheThings/SQL Injection at master · swisskyrepo/PayloadsAllTheThingsGitHub

Last updated 4 months ago

Was this helpful?