Database Reference
In-Depth Information
This may be a problem if we require all our
last_name
entries to be in upper case! We can catch and
correct this problem with a
BEFORE UPDATE OR INSERT
trigger. In the middle of our trigger, we might say:
:new.last_name := upper(:new.last_name);
This will capitalize the new value we are submitting for last_name. If we want to complain that the user is
trying to update the last name with the same last name that already exists, we can compare the
capitalized new value with the old value, like this:
IF :new.last_name = :old.last_name
THEN
Raise_Application_Error(-20000, 'Same last name as before!');
A
BEFORE UPDATE
trigger has access to both the existing value in the database (
:old
) and the new value
being submitted (
:new
). That ability is often utilized in triggers.
Avoiding SQL Injection
If a computer user saves the html source of a web page that submits data and is able to modify the web
page to send data that is not normally allowed, that would be an example of cross-sight scripting (the
user's own web page being one site, submitting data to the web server being another site.) For example,
you might have a web page that submits a ZIP code for an address and only allows numeric data. I might
maliciously modify the web page so my copy submits a web link (URL) in the ZIP code field. The only
real prevention for cross-site scripting is to assume it will always happen and take steps on the server to
catch and handle it.
Perhaps your web page submits data to Oracle database, and a malicious user modifies a copy of
your web page to submit Oracle SQL or PL/SQL commands in the zip code field. That hacker might put
this code in the field, “
11111;delete from employees;--
”. If you are building a dynamic query that
simply embeds the submitted data in a query, then instead of executing:
UPDATE EMPLOYEES SET ZIP=
11111
WHERE EMPLOYEE_ID=300;
You might execute this set of commands:
UPDATE EMPLOYEES SET ZIP=
11111;delete from employees;--
WHERE EMPLOYEE_ID=300;
That works out to three lines: an update for all employees, deleting all records from employees, and
a comment. That is an example of SQL injection.
A typical SQL injection attack modifies a select statement by tacking on an extra where test that is
true for all data. For example, if I accept user input of a last name to search employees and the user types
“King' or 'a'='a”, my dynamic SQL might look like this:
SELECT * FROM EMPLOYEES WHERE LAST_NAME='
King' or 'a'='a
';
If this were a test for password matching a value stored in Oracle database, then the SQL injection
might look like this:
SELECT count(*) FROM EMPLOYEES WHERE LAST_NAME='King' and
PASSWORD='
whatever' or 'a'='a
';
The select statement will return a number greater than 0 and the user might get access even though
he doesn't know the password.
At the Oracle database, you can prevent SQL injection from happening by several methods. One
traditional way is to filter the incoming data and/or escape the data (make it a sequence of individual
