OCL1 Oracle 10 g : SQL & PL/SQL Session #10

OCL1 Oracle 10g:SQL & PL/SQLSession #10 Matthew P. Johnson CISDD, CUNY Fall, 2004 Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Agenda • Web apps & security • Oracle & XML • RegEx support in 10g • More on the PL/SQL labs • Today’s lab Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Review: Why security is hard • It’s a “negative deliverable” • It’s an asymmetric threat • Tolstoy: “Happy families are all alike; every unhappy family is unhappy in its own way.” • Analogs: “homeland”, jails, debugging, proof-reading, Popperian science, fishing, MC algs • So: fix biggest problems first Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Injection attacks – DB web apps • Consider another input: • user: your-boss • pass: ' OR 1=1 OR pass = '  SELECT * FROM users WHERE user = u AND pass = p; http://pages.stern.nyu.edu/~mjohnson/dbms/perl/login.cgi Copy from: http://pages.stern.nyu.edu/~mjohnson/dbms/perl/injection.txt SELECT * FROM users WHERE user = 'your-boss' AND pass = '' OR 1=1 OR pass = ''; SELECT * FROM users WHERE user = 'your-boss' AND password = '' OR 1=1 OR pass = ''; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Multi-command injection attacks • Consider another input: • user: '; DROP TABLE users; SELECT FROM users WHERE pass = ' • pass: abc  SELECT * FROM users WHERE user = u AND pass = p; SELECT * FROM users WHERE user = ''; DROP TABLE users; SELECT FROM users WHERE pass = '' AND pass = 'abc'; SELECT * FROM users WHERE user = ''; DROP TABLE users; SELECT FROM users WHERE password = '' AND password = 'abc'; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Multi-command injection attacks • Consider another input: • user: '; SHUTDOWN WITH NOWAIT; SELECT FROM users WHERE pass = ' • pass: abc  SELECT * FROM users WHERE user = u AND pass = p; SELECT * FROM users WHERE user = ''; SHUTDOWN WITH NOWAIT; SELECT FROM users WHERE pass = '' AND pass = 'abc'; SELECT * FROM users WHERE user = ''; SHUTDOWN WITH NOWAIT; SELECT FROM users WHERE password = '' AND password = 'abc'; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Injection attacks – other inputs • Consider another input: • user: ' OR 1=1 OR user = ' • pass: ' OR 1=1 OR user = '  Delete everyone! DELETE FROM users WHERE user = u AND pass = p; DELETE FROM users WHERE user = '' OR 1=1 OR user = '' AND pass = '' OR 1=1 OR user = ''; http://pages.stern.nyu.edu/~mjohnson/dbms/perl/users.cgi DELETE FROM users WHERE user = '' OR 1=1 OR user = '' AND pass = '' OR 1=1 OR user = ''; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Preventing injection attacks • Source of problem (in SQL case): use of quotes • Soln 1: don’t allow quotes! • Reject any entered data containing single quotes • Q: Is this satisfactory? • Does Amazon need to sell O’Reilly books? • Soln 2: escape any single quotes • Replace any ‘ with a ‘’ or \’ • In PHP, turn on magic_quotes_gpc flag in .htaccess • show both versions Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Preventing injection attacks • When to do security checking for quotes, etc.? • Natural choice: in client-side data validation • But not enough! • As saw: can still manually submit GET and POST •  Must do security checking on server Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Preventing injection attacks • Soln 3: use prepare parameterized queries • Supported in JDBC, Perl DBI, PHP ext/mysqli • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/loginsafe.cgi • http://pages.stern.nyu.edu/~mjohnson/dbms/perl/userssafe.cgi • Very dangerous: using tainted data to run commands at the Unix command prompt • Semi-colons, prime char, etc. • Safest: define set if legal chars, not illegal ones Matthew P. Johnson, OCL1, CISDD CUNY, F2004

More Info • phpGB MySQL Injection Vulnerability • http://www.securiteam.com/unixfocus/6X00O1P5PY.html • "How I hacked PacketStorm“ • http://www.wiretrip.net/rfp/txt/rfp2k01.txt Matthew P. Johnson, OCL1, CISDD CUNY, F2004

And now for something completely different: XML • XML: eXtensible Mark-up Language • Very popular language for semi-structured data • Mark-up language: consists of elements composed of tags, like HTML • Emerging lingua franca of the Internet, Web Services, inter-vender comm Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Unstructured data • At one end of continuum: unstructured data • Text files • Stock market prices • CIA intelligence intercepts • Audio recordings • “Just one damn bit after another” • Henry Ford • No (intentional, formal) patterns to the data • Difficult to manage/make sense of • Why we need data-mining Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Structured data • At the other end: structured data • Tables in RDBMSs • Data organized into semantic chunks • entities • Similar/related entities grouped together • Relationships, classes • Entities in same group have same structure • Same fields/attributes/properties • Easy to make sense of • But sometimes too rigid a req. • Difficult to send—convert to tab-delimited Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Semi-structured data • Not too random • Data organized into entities • Similar/related grouped to form other entities • Not too structured • Some attributes may be missing • Size of attributes may vary • Support of lists/sets • Juuust Right • Data is self-describing Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Semi-structured data • Predominant examples: • HTML: HyperText Mark-up Language • XML: eXtensible Mark-up Language • NB: both mark-up languages (use tags) • Mark-up lends self of semi-structured data • Demarcate boundaries for entities • But freely allow other entities inside Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Data model for semi-structured data • Usually represented as directed graphs • Graph: set of vertices (nodes) and edges • Dots connected by lines; not nec. a tree! • In model, • Nodes ~ entities or fields/attributes • Edges ~ attribute-of/sub-entity-of • Example: publisher publishes >=0 books • Each book has one title, one year, >=1 authors • Draw publishers graph Matthew P. Johnson, OCL1, CISDD CUNY, F2004

XML is a SSD language • Standard published by W3C • Officially announced/recommended in 1998 • XML != HTML • XML != a replacement for HTML • Both are mark-up languages • Big diffs: • XML doesn’t use predefined tags (!) • But it’s extensible: tags can be added • HTML is about presentation: , , • XML is about content: <book>, <author> Matthew P. Johnson, OCL1, CISDD CUNY, F2004

XML syntax • Like HTML in many respects but more strict • All tags must be closed • Can’t have: this is a line • Every start tag has an end tag • Although style can replace both • IS case-sensitive • IS space-sensitive • XML doc has a unique root element Matthew P. Johnson, OCL1, CISDD CUNY, F2004

XML syntax • Tags must be properly nested • Not allowed I’m not kidding • Intuition: file folders • Elements may have quoted attributes • <Myelm myatt=“myval”>…</Myelm> • Comments same as in HTML: •  • Draw publishers XML Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Escape chars in XML • Some chars must be escaped • Distinguish content from syntax • Can also declare value to be pure text: <elm>3 < 5</elm> <elm>"Don't call me 'Shirley'!"</elm> <aRealTag> <![CDATA[<notAtag>jsdljsd<neitherAmI<“’><>>]]></aRealTag> Matthew P. Johnson, OCL1, CISDD CUNY, F2004

XML Namespaces • Different schemas/DTDs may overlap • XHTML and MathML share some tags • Soln: namespaces • as in Java/C++/C# <bookxmlns:isbn=“www.isbn-org.org/def”> <title> … </title> <number> 15 </number> <isbn:number> …. </isbn:number> </book> Matthew P. Johnson, OCL1, CISDD CUNY, F2004

<persons> <row> <name>John</name> <phone> 3634</phone></row> <row> <name>Sue</name> <phone> 6343</phone> <row> <name>Dick</name> <phone> 6363</phone></row> </persons> From Relational Data to XML Data XML: persons persons row row row phone name phone name phone name “John” 3634 “Sue” 6343 “Dick” 6363 Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Semi-structured Data Explained • List-valued attributes • XML is not 1NF! • Impossible in (single) tables: <person> <name> Mary</name> <phone>2345</phone> <phone>3456</phone> </person>  two phones ! ??? Matthew P. Johnson, OCL1, CISDD CUNY, F2004

<movieinfo> <movieid=“o111”> <title>Lost in Translation</title> <year>2003</year> <starsidref=“o333 o444”/> </movie> <movieid=“o222”> <title>Hamlet</title> <year>1999</year> <starsidref=“o333”/> </movie> <personid=“o456”> <personid=“o111”> <name>Bill Murray</name> <moviesidref=“o111 o222”/> </person> </movieinfo> Object ids and References • SSD graph might not be trees! • But XML docs must be • Would cause much redundancy • Soln: same concept as pointers in C/C++/J • Object ids and references • Graph example: • Movies: Lost in Translation, Hamlet • Stars: Bill Murray, Scarlet Johansson Matthew P. Johnson, OCL1, CISDD CUNY, F2004

What do we do with XML? • Things done with XML: • Send to partners • Parse XML received • Convert to RDBMS rows • Query for particular data • Convert to other XML • Convert to formats other than XML • Lots of tools/standards for these… Matthew P. Johnson, OCL1, CISDD CUNY, F2004

DTDs & understanding XML • XML is extensible • Advantage: when creating, we can use any tags we like • Disadv: when reading, they can use any tags they like • Using XML docs a priori is very difficult • Solution: impose some constraints Matthew P. Johnson, OCL1, CISDD CUNY, F2004

DTDs • DTD: Document Type Definition • You and partners/vertical industry/academic discipline decide on a DTD/schema for your docs • Specify which entities you may use/must understand • Specify legal relationships • DTD specifies the grammar to be used • DTD = set of rules for creating valid entities • DTD tells your software what to look for in doc Matthew P. Johnson, OCL1, CISDD CUNY, F2004

DTD examples • Well-formed XML v. valid XML • Simple example: • http://pages.stern.nyu.edu/~mjohnson/dbms/xml/note.xml • http://pages.stern.nyu.edu/~mjohnson/dbms/xml/badnote.xml • http://pages.stern.nyu.edu/~mjohnson/dbms/xml/badnote2.xml • Copy from: http://pages.stern.nyu.edu/~mjohnson/dbms/eg/xml.txt • Partial publisher example rules: • Root  publisher • Publisher  name, book*, author* • Book  title, date, author+ • Author  firstname, middlename?, lastname Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Partial DTD example (typos!) <?xml version=“1.0” encoding=“UTF-8” ?> <!DOCTYPE PUBLISHER [ <!ELEMENT PUBLISHER (name, book*, author*)> <!ELEMENT name (#PCDATA)> <!ELEMENT BOOK (title, date, author+)> <!ELEMENT AUTHOR (firstname, middlename?, lastname> <!ELEMENT firstname (#PCDATA)> <!ELEMENT lastname (#PCDATA)> <!ELEMENT middlename (#PCDATA)> • DTD is not XML, but can be embedded in or ref.ed from XML • Replacement for DTDs is XML Schemas Matthew P. Johnson, OCL1, CISDD CUNY, F2004

XML Applications/dialects • MathML: Mathematical Markup Language • http://wwwasdoc.web.cern.ch/wwwasdoc/WWW/publications/ictp99/ictp99N8059.html • VoiceXML: http://newmedia.purchase.edu/~Jeanine/interfaces/rps.xml • ChemML: Chemical Markup Language • XHMTL: HTML retrofitted as an XML application Matthew P. Johnson, OCL1, CISDD CUNY, F2004

SQL*Plus settings SQL> SET RECSEP OFF SQL> COLUMN text FORMAT A60 Matthew P. Johnson, OCL1, CISDD CUNY, F2004

XML in Oracle - purchase-order example <?xml version="1.0"?> <purchase_order> <customer_name>Alpha Tech</customer_name> <po_number>11257></po_number> <po_date>2004-01-20</po_date> <po_items> <item> <part_number>AI5-4557</part_number> <quantity>20</quantity> </item> <item> <part_number>EI-T5-001</part_number> <quantity>12</quantity> </item> </po_items> </purchase_order> Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Storing XML data • As of 9i, has XMLType data type • By default, underlying storage is as CLOB CREATE TABLE purchase_order( po_id number(5) not null, customer_po_nbr varchar(20), customer_inception_date date, order_nbr number(5), purchase_order_doc xmltype, constraint purchase_order_pk primary key(po_id) ); Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Loading XML into Oracle • First, log in as sys: • Now scott can import: connect sys/junk as sysdba create directory xml_data as '/xml‘; grant read, write on directory xml_data to scott; connect scott/tiger declare bf1 bfile; begin bf1 := bfilename('XML_DATA', 'purch_ord.xml'); insert into purchase_order(po_id, purchase_order_doc) values(1000, xmltype(bf1, nls_charset_id('we8mswin1252'))); end; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Loading XML into Oracle • Not just loading raw text • XMLType data must be well-formed • Parsable as XML • Try modifying customer_name open tag Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Accessing XML in Oracle • Now can look at raw XML: • Can also use XPath to extract particular nodes and values, with extract function: SQL> SELECT purchase_order_doc FROM purchase_order; SQL> SELECT extract(purchase_order_doc, '/purchase_order/customer_name') FROM purchase_order; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

XPath in Oracle • Can also extract all nodes of one type, underneath some node, with double-slash // • All purchase order items • NB: this is not valid XML • No unique root • Can request just one with bracket op • Numbering starts at 1, not 0 • Wrong name/number  no error, no results SQL> SELECT extract(purchase_order_doc, '/purchase_order//item') FROM purchase_order; SQL> SELECT extract(purchase_order_doc, '/purchase_order/po_items/item[1]') FROM purchase_order; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

extract v. extractvalue • extractvalue returns value, not whole node: vs. • extractvalue applies only to unique nodes: SQL> SELECT extract(purchase_order_doc, '/purchase_order/customer_name') FROM purchase_order; SQL> SELECT extractvalue(purchase_order_doc, '/purchase_order/customer_name') FROM purchase_order; SQL> SELECT extractvalue(purchase_order_doc, '/purchase_order/po_items') FROM purchase_order; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

existsnode function • Can check whether node/location exists with existnode function • Returns 1 or 0 • Also applies to bracketed paths: SQL> SELECT po_id FROM purchase_order WHERE existsnode(purchase_order_doc, '/purchase_order/customer_name') = 1; SQL> SELECT po_id FROM purchase_order WHERE existsnode(purchase_order_doc, '/purchase_order/po_items/item[1]') = 1; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Moving data from XML to relations • To move single values from XML to tables, can simply use extractvalue in UPDATE statements: SQL> UPDATE purchase_order SET order_nbr = 7101, customer_po_nbr = extractvalue(purchase_order_doc, '/purchase_order/po_number'), customer_inception_date = to_date(extractvalue(purchase_order_doc, '/purchase_order/po_date'), 'yyyy-mm-dd'); Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Moving data from XML to relations • What about moving set of nodes • The two item nodes • Use xmlsequence to get a varray of items • Use TABLE to convert to a relation SQL> SELECT extract(purchase_order_doc, '/purchase_order//item') FROM purchase_order; SQL> SELECT rownum, item.* FROM TABLE( SELECT xmlsequence(extract(purchase_order_doc, '/purchase_order//item')) FROM purchase_order) item; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Moving data from XML to relations • Result is a two-row relation with XMLTypes • Can use extractvalue to extract this data • First, create destination table: CREATE TABLE LINE_ITEM( ORDER_NBR NUMBER(9) NOT NULL, PART_NBR VARCHAR2(20) NOT NULL, QTY NUMBER(5) NOT NULL, FILLED_QTY NUMBER(5), CONSTRAINT line_item_pk PRIMARY KEY (ORDER_NBR,PART_NBR)); Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Moving data from XML to relations • Then insert results: SQL> INSERT INTO line_item(order_nbr,part_nbr,qty) SELECT 7109, extractvalue(column_value, '/item/part_number'), extractvalue(column_value, '/item/quantity') FROM TABLE( SELECT xmlsequence(extract(purchase_order_doc, '/purchase_order//item')) FROM purchase_order ); Matthew P. Johnson, OCL1, CISDD CUNY, F2004

XML Schemas and Oracle • By default, XML must be well-formed to be read into the XMLType field • XML is valid if it conforms to a schema • To use a schema with Oracle, must first register it: declare bf1 bfile; begin bf1 := bfilename('XML_DATA', 'purch_ord.xsd'); dbms_xmlschema.registerschema( 'http://localhost:8080/home/xml/schemas/purch_ord.xsd', bf1); end; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

XML Schemas and Oracle • With schema registered, can apply it to an XMLType field CREATE TABLE purchase_order2 (po_id NUMBER(5) NOT NULL, customer_po_nbr VARCHAR2(20), customer_inception_date DATE, order_nbr NUMBER(5), purchase_order_doc XMLTYPE, CONSTRAINT purchase_order2_pk PRIMARY KEY (po_id)) XMLTYPE COLUMN purchase_order_doc XMLSCHEMA "http://localhost:8080/home/xml/schemas/purch_ord.xsd" ELEMENT "purchase_order"; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Importing to schema field • Try to import xml file, get error: declare bf1 bfile; begin bf1 := bfilename('XML_DATA', 'purch_ord.xml'); insert into purchase_order2(po_id, purchase_order_doc) values (2000, XMLTYPE(bf1, nls_charset_id('WE8MSWIN1252'))); end; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Importing to schema field • Root node of XML must specify the schema • Change root to the following: • Now can import • Also fails if extra or missing nodes • Modify company_name node • Add new comments node <purchase_order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://localhost:8080/home/xml/schemas/purch_ord.xsd"> Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Can check to see whether schema is used • Can call isSchemaBased(), getSchemaURL() and isSchemaValid() on XMLType fields: SQL> select po.purchase_order_doc.isSchemaBased(), po.purchase_order_doc.getSchemaURL(), po.purchase_order_doc.isSchemaValid() from purchase_order2 po; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

Updating XMLType data • Can update XMLType data with ordinary UPDATE statements: • Replaces whole XMLType object with new one SQL> UPDATE purchase_order po SET po.purchase_order_doc = XMLTYPE(BFILENAME('XML_DATA', 'purch_ord_alt.xml'), nls_charset_id('WE8MSWIN1252')) WHERE po.po_id = 2000; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

OCL1 Oracle 10 g : SQL & PL/SQL Session #10