MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘sql’ Category

Provision Oracle 12c PDB

with 8 comments

Somebody wants help provisioning a pluggable database (PDB). While it’s a new concept in the Oracle 12c database, I can sympathize with their plight. It wasn’t clear to me when first working with it, and I couldn’t seem to find a quick tutorial on the web. While it’s in the new Oracle 12c PL/SQL Programming Book, that won’t be out until January 2014.

If you’re new to Oracle 12c, which is most of us, then it’s important to understand that PDB is a private data context. Most of it’s data catalog is private and separate from the overall database. Only a small portion of the database catalog is stored in the container database catalog, and new CDB_ administrative views are added to the database. A PDB is a great solution when you’re leveraging the Editioning feature of Oracle 11g database.

You should note the guarantee from page #9 of Oracle’s Oracle Multitenant:

From the point of view of the client connecting via Oracle Net, the PDB is the database. A PDB is fully compatible with a non-CDB. We shall refer to this from now on as the PDB/non-CDB compatibility guarantee. In other words, the installation scheme for an application backend that ran without error against a non-CDB will run, with no change, and without error, in a PDB and will produce the same result.

Here are the steps that work on both Windows, Linux, or Windows:

  1. The following SQL command lets you create a pluggable database (PDB) with a video user assigned to it:
1
2
3
4
5
6
7
CREATE PLUGGABLE DATABASE videodb
  ADMIN USER videoadm IDENTIFIED BY Video1
  ROLES = (dba)
  DEFAULT TABLESPACE videots
    DATAFILE 'C:\APP\ORACLE\ORADATA\ORCL\VIDEO01.DBF' SIZE 500M ONLINE
  FILE_NAME_CONVERT = ('C:\APP\ORACLE\ORADATA\ORCL\PDBSEED\',
                       'C:\APP\ORACLE\ORADATA\ORCL\VIDEOPDB\');

Don’t try to create the DEFAULT TABLESPACE before you provision the database. If you do, you’ll get the following exception:

CREATE PLUGGABLE DATABASE videodb
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01537: cannot add file 'C:\APP\ORACLE\ORADATA\ORCL\VIDEO01.DBF' - file already part of database
  1. You need to stop the Oracle listener and modify the listener.ora file. Lines 9 through 12 configure a VIDEODB Oracle SID. After you make the changes, start the Oracle listener.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\oracle\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC =
      (SID_NAME = VIDEODB)
      (ORACLE_HOME = C:\app\oracle\product\12.1.0\dbhome_1)
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )
  1. You also need to add a VIDEO TNS alias to the tnsnames.ora file for the VIDEODB pluggable database (PDB).
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )
 
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
 
VIDEO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = videodb)
    )
  )
  1. You connect as the SYSDBA for the VIDEO pluggable database with the following syntax (not presently an example in the SQL*Plus Guide). After connecting as the SYSDBA for the VIDEODB, you’ll be prompted for a password. The required password is the Video1 password that you used when you set up the VIDEODB database.
sqlplus sys@VIDEO AS sysdba
  1. After authenticating as the SYSDBA, you need to start the VIDEODB pluggable database, like:
SQL> startup
Pluggable DATABASE opened.

You can also start the pluggable database from the generic SYSDBA account. You only need to change the CONTAINER context to pluggable database. You can find the status of pluggable databases with the following query:

SQL> COLUMN RESTRICTED FORMAT A10
SQL> SELECT   v.name
  2  ,        v.open_mode
  3  ,        NVL(v.restricted, 'n/a') AS restricted
  4  ,        d.STATUS
  5  FROM     v$PDBs v INNER JOIN dba_pdbs d USING(guid)
  6  ORDER BY v.create_scn;

The generic sample database returns this after restarting the database:

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- --------
PDB$SEED                       READ ONLY  NO         NORMAL
PDBORCL                        MOUNTED    n/a        NORMAL

The following changes the CONTAINER context, opens the pdborcl PDB, and creates a traditional Oracle 11g and backward standard user/schema account in the pluggable database.

SQL> ALTER SESSION SET container=pdborcl;
SQL> ALTER PLUGGABLE DATABASE pdborcl OPEN;
SQL> CREATE USER johnny IDENTIFIED BY johnny;

Re-query the status of the pluggable databases, and you’ll see:

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- --------
PDB$SEED                       READ ONLY  NO         NORMAL
PDBORCL                        READ WRITE NO         NORMAL

If you have more than one pluggable database, you can use the following to open them all as the CDB’s SYSDBA:

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
  1. As Oracle said during the Oracle 11gR2 release, the DBA role no longer grants UNLIMITED TABLESPACE. That means you need to grant it as the SYSDBA for the PDB, like:
GRANT UNLIMITED TABLESPACE TO videoadm;
  1. After you’ve done all the prior steps, you can connect with the following as the Administrative VIDEO user:
sqlplus videoadm@VIDEO/Video1

Although making such a connection is a poor choice, it would be better to make a secure connection like the one below. The problem with the former is that it exposes information at the OS command line that would be visible to other users. Connecting to the Oracle SQL*Plus with the /NOLOG option prevents disclosure of that information.

C:\Users\mclaughlinm>sqlplus /nolog
 
SQL*Plus: Release 12.1.0.1.0 Production ON Tue Aug 13 01:28:30 2013
 
Copyright (c) 1982, 2013, Oracle.  ALL rights reserved.
 
SQL> CONNECT videoadm@VIDEO/Video1
Connected.
SQL> SHOW USER
USER IS "VIDEOADM"
  1. Once you’re connected as the SYSDBA role, you can create standard (pre-Oracle 12c) user/schemas with the old syntax. Below, we create a student account:
SQL> CREATE USER student IDENTIFIED BY student;
SQL> GRANT CREATE cluster, CREATE indextype, CREATE operator
  2  ,     CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION
  3  ,     CREATE TABLE, CREATE TRIGGER, CREATE TYPE
  4  ,     CREATE VIEW, UNLIMITED TABLESPACE TO student;

Naturally, you can also add a USER from the CDB’s SYSDBA role when in the proper CONTAINER context.

This has shown you how to provision a pluggable database (PDB). As a side note, you should know that you can only create user-defined common users (at the CDB-level) with a c## prefix.

An addendum: DBMS_COMPARE isn’t provisioned automatically, and you need to manually apply it in your ADMIN user for the PDB. I’ve blogged about it here.

Written by maclochlainn

September 13th, 2013 at 1:51 am

DBMS_COMPARISON Missing?

with 4 comments

The dbms_comparison package isn’t deployed when you provision a pluggable databases (PDBs) in Oracle 12c. It appears to be a simple omission. At least, it let me manually compiled the dbms_comparison package with this syntax:

@?/rdbms/admin/dbmscmp.SQL
@?/rdbms/admin/prvtcmp.plb

However, when I ran the code against the PDB it failed. The same code worked against a container database (CDB). It struck me as odd. The error stack wasn’t too useful, as you can see below:

1
2
3
4
5
6
7
8
9
10
BEGIN
*
ERROR at line 1:
ORA-06564: object "SYS"."COMPARE_NAME" does NOT exist
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 569
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 602
ORA-06512: at "SYS.DBMS_CMP_INT", line 394
ORA-01403: no DATA found
ORA-06512: at "SYS.DBMS_COMPARISON", line 764
ORA-06512: at line 2

My test was using two copies of a table with differences between column values. Both were deployed in the same CDB or PDB. That meant it was either a missing table or a problem with my database link. Here’s the statement that caused the failure:

7
8
9
10
11
12
  dbms_comparison.create_comparison(comparison_name      => 'COMPARE_NAME'
                                   , schema_name         => 'video'
                                   , object_name         => 'MEMBER#1'
                                   , dblink_name         => 'loopbackpdb'
                                   , remote_schema_name  => 'video'
                                   , remote_object_name  => 'MEMBER#2');

Unfortunately, there wasn’t any notable difference between the two database links. Playing around with it, I discovered the problem. While you don’t have to enclose your case sensitive password in double quotes for a CDB database link, you do need to enclose the password with double quotes in a PDB database link.

This database link fixed the problem:

1
2
3
CREATE DATABASE LINK loopbackpdb
  CONNECT TO video IDENTIFIED BY "Video1"
  USING 'video';

The delimiting double quotes on line 2 fixed the problem. Hopefully, this helps somebody who runs into it too. Any way, according to this June 2013 Oracle White Paper it would appear as a bug because it’s an inconsistent behavior between a CDB and PDB.

Written by maclochlainn

August 14th, 2013 at 2:18 am

Posted in Oracle,Oracle 12c,pl/sql,sql

Tagged with , ,

Convert LONG to CLOB

without comments

A friend asked me how to get an Oracle view definition out of a LONG column and into a web application. I thought it was an interesting question because I ran into a similar problem when writing the Oracle Database 12c PL/SQL Programming book.

One of the new Oracle 12c features is the DBMS_UTILITY‘s new EXPAND_SQL_TEXT procedure. It lets you expand a view’s definition to include any views that the master view uses. It produces a single queries with all the base tables that support the view. Clearly, it’s an effective tool when it comes to understanding how those large ERP views work in the E-Business Suite.

LONG to CLOB Data Type

Here’s a version of the function that converts the LONG data type into a CLOB data type:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
CREATE OR REPLACE FUNCTION long_to_clob
( pv_view_name      VARCHAR2
, pv_column_length  INTEGER )
RETURN CLOB AS
 
  /* Declare local variables. */
  lv_cursor    INTEGER := dbms_sql.open_cursor;
  lv_feedback  INTEGER;         -- Acknowledgement of dynamic execution
  lv_length    INTEGER;          -- Length of string
  lv_return    CLOB;            -- Function output
  lv_stmt      VARCHAR2(2000);  -- Dynamic SQL statement
  lv_string    VARCHAR2(32760); -- Maximum length of LONG data type
 
BEGIN
  /* Create dynamic statement. */
  lv_stmt := 'SELECT text'||CHR(10)
          || 'FROM user_views'||CHR(10)
          || 'WHERE view_name = '''||pv_view_name||'''';
 
  /* Parse and define a long column. */
  dbms_sql.parse(lv_cursor, lv_stmt, dbms_sql.native);
  dbms_sql.define_column_long(lv_cursor,1);
 
  /* Only attempt to process the return value when fetched. */
  IF dbms_sql.execute_and_fetch(lv_cursor) = 1 THEN
    dbms_sql.column_value_long(
        lv_cursor
      , 1
      , pv_column_length
      , 0
      , lv_string
      , lv_length);
  END IF;
 
  /* Check for an open cursor. */
  IF dbms_sql.is_open(lv_cursor) THEN
    dbms_sql.close_cursor(lv_cursor);
  END IF;
 
  /* Create a local temporary CLOB in memory:
     - It returns a constructed lv_return_result.
     - It disables a cached version.
     - It set the duration to 12 (the value of the dbms_lob.call
       package-level variable) when the default is 10. */
  dbms_lob.createtemporary(lv_return, FALSE, dbms_lob.CALL);
 
  /* Append the Long to the empty temporary CLOB. */
  dbms_lob.WRITE(lv_return, pv_column_length, 1, lv_string);
 
  RETURN lv_return;
END long_to_clob;
/

This wraps the conversion of a LONG to CLOB, which is necessary to pre-size the LONG data type. Pre-sizing avoids reading the LONG column’s value character-by-character.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE OR REPLACE FUNCTION expand_view 
( pv_view_name  VARCHAR2 ) RETURN CLOB IS
 
  /* Declare containers for views. */
  lv_input_view   CLOB;
  lv_output_view  CLOB;
 
  /* Declare a target variable,  because of the limit of SELECT-INTO. */
  lv_long_view  LONG;
 
  /* Declare a dynamic cursor. */
  CURSOR c (cv_view_name VARCHAR2) IS
    SELECT   text
    FROM     user_views
    WHERE    view_name = cv_view_name;
 
BEGIN
  /* Open, fetch, and close cursor to capture view text. */
  OPEN c(pv_view_name);
  FETCH c INTO lv_long_view;
  CLOSE c;
 
  /* Convert a LONG return type to a CLOB. */
  lv_input_view := long_to_clob(pv_view_name, LENGTH(lv_long_view));
 
  /* Send in the view text and receive the complete text. */
  dbms_utility.expand_sql_text(lv_input_view, lv_output_view);
 
  /* Return the output CLOB value. */
  RETURN lv_output_view;
END;
/

LONG to VARCHAR2 Data Type

Here’s a version of the function that converts the LONG data type into a VARCHAR2 data type:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
CREATE OR REPLACE FUNCTION long_to_varchar2
( pv_view_name      VARCHAR2
, pv_column_length  INTEGER )
RETURN VARCHAR2 AS
 
  /* Declare local variables. */
  lv_cursor    INTEGER := dbms_sql.open_cursor;
  lv_feedback  INTEGER;          -- Acknowledgement of dynamic execution
  lv_length    INTEGER;          -- Length of string
  lv_return    VARCHAR2(32767);  -- Function output
  lv_stmt      VARCHAR2(2000);   -- Dynamic SQL statement
  lv_string    VARCHAR2(32760);  -- Maximum length of LONG data type
 
BEGIN
  /* Create dynamic statement. */
  lv_stmt := 'SELECT text'||CHR(10)
          || 'FROM user_views'||CHR(10)
          || 'WHERE view_name = '''||pv_view_name||'''';
 
  /* Parse and define a long column. */
  dbms_sql.parse(lv_cursor, lv_stmt, dbms_sql.native);
  dbms_sql.define_column_long(lv_cursor,1);
 
  /* Only attempt to process the return value when fetched. */
  IF dbms_sql.execute_and_fetch(lv_cursor) = 1 THEN
    dbms_sql.column_value_long(
        lv_cursor
      , 1
      , pv_column_length
      , 0
      , lv_string
      , lv_length);
  END IF;
 
  /* Check for an open cursor. */
  IF dbms_sql.is_open(lv_cursor) THEN
    dbms_sql.close_cursor(lv_cursor);
  END IF;
 
  /* Convert the long length string to a maximum size length. */
  lv_return := lv_string;
 
  RETURN lv_return;
END long_to_varchar2;
/

This wraps the conversion of a LONG to VARCHAR2, which is necessary to pre-size the LONG data type. Pre-sizing avoids reading the LONG column’s value character-by-character.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE OR REPLACE FUNCTION return_view_text 
( pv_view_name  VARCHAR2 ) RETURN VARCHAR2 IS
 
  /* Declare a target variable,  because of the limit of SELECT-INTO. */
  lv_long_view  LONG;
 
  /* Declare a dynamic cursor. */
  CURSOR c (cv_view_name VARCHAR2) IS
    SELECT   text
    FROM     user_views
    WHERE    view_name = cv_view_name;
 
BEGIN
  /* Open, fetch, and close cursor to capture view text. */
  OPEN c(pv_view_name);
  FETCH c INTO lv_long_view;
  CLOSE c;
 
  /* Return the output CLOB value. */
  RETURN long_to_varchar2(pv_view_name, LENGTH(lv_long_view));
END;
/

Wrapper to DBMS_UTILITY‘s EXPAND_SQL_TEXT Procedure

As a response to somebody who simply wants a wrapper to the new dbms_utility‘s expand_sql_text procedure, I wrote the wrapper. Although, my reflection on this is why does a new procedure require a new wrapper to be useful? Did the use case get stated incorrectly. Anyway, here’s the wrapper:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
-- Converts a long column to a CLOB data type.
CREATE OR REPLACE FUNCTION expand_sql_text
( pv_view_name      VARCHAR2 )
RETURN CLOB AS
 
  /* Declare containers for views. */
  lv_input_view   CLOB;
  lv_output_view  CLOB;
 
  /* Declare a target variable,  because of the limit of SELECT-INTO. */
  lv_long_view  LONG;
 
  /* Declare local variables for dynamic SQL. */
  lv_cursor    INTEGER := dbms_sql.open_cursor;
  lv_feedback  INTEGER;         -- Acknowledgement of dynamic execution
  lv_length    INTEGER;          -- Length of string
  lv_return    CLOB;            -- Function output
  lv_stmt      VARCHAR2(2000);  -- Dynamic SQL statement
  lv_string    VARCHAR2(32760); -- Maximum length of LONG data type
 
  /* Declare user-defined exception. */
  invalid_view_name  EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_view_name, -20001);
 
  /* Declare a dynamic cursor. */
  CURSOR c (cv_view_name VARCHAR2) IS
    SELECT   text
    FROM     user_views
    WHERE    view_name = cv_view_name;
 
  FUNCTION verify_view_name
  ( pv_view_name      VARCHAR2 )
  RETURN BOOLEAN AS
    /* Default return value. */
    lv_return_result  BOOLEAN := FALSE;
 
    /* Declare cursor to check view name. */
    CURSOR c (cv_view_name  VARCHAR2) IS
      SELECT   NULL
      FROM     user_views
      WHERE    view_name = cv_view_name;
  BEGIN
    FOR i IN c (pv_view_name) LOOP
      lv_return_result := TRUE;
    END LOOP;
 
    RETURN lv_return_result;
  END verify_view_name;
BEGIN
  /* Throw exception when invalid view name. */
  IF NOT verify_view_name(pv_view_name) THEN
     RAISE invalid_view_name;
  END IF;
 
  /* Open, fetch, and close cursor to capture view text. */
  OPEN c(pv_view_name);
  FETCH c INTO lv_long_view;
  CLOSE c;
 
  /* Create dynamic statement. */
  lv_stmt := 'SELECT text'||CHR(10)
          || 'FROM user_views'||CHR(10)
          || 'WHERE view_name = '''||pv_view_name||'''';
 
  /* Parse and define a long column. */
  dbms_sql.parse(lv_cursor, lv_stmt, dbms_sql.native);
  dbms_sql.define_column_long(lv_cursor,1);
 
  /* Only attempt to process the return value when fetched. */
  IF dbms_sql.execute_and_fetch(lv_cursor) = 1 THEN
    dbms_sql.column_value_long(
        lv_cursor
      , 1
      , LENGTH(lv_long_view)
      , 0
      , lv_string
      , lv_length);
  END IF;
 
  /* Check for an open cursor. */
  IF dbms_sql.is_open(lv_cursor) THEN
    dbms_sql.close_cursor(lv_cursor);
  END IF;
 
  /* Create a local temporary CLOB in memory:
     - It returns a constructed lv_return_result.
     - It disables a cached version.
     - It set the duration to 12 (the value of the dbms_lob.call
       package-level variable) when the default is 10. */
  dbms_lob.createtemporary(lv_input_view, FALSE, dbms_lob.CALL);
 
  /* Append the Long to the empty temporary CLOB. */
  dbms_lob.WRITE(lv_input_view, LENGTH(lv_long_view), 1, lv_string);
 
  /* Send in the view text and receive the complete text. */
  dbms_utility.expand_sql_text(lv_input_view, lv_output_view);
 
  /* Return the output CLOB value. */
  RETURN lv_output_view;
EXCEPTION
  WHEN invalid_view_name THEN
    RAISE_APPLICATION_ERROR(-20001,'Invalid View Name.');
  WHEN OTHERS THEN
    RETURN NULL;
END expand_sql_text;
/

As always, I hope this provides folks with a leg up on tricky syntax.

Written by maclochlainn

August 6th, 2013 at 11:56 pm

SQL Like Comparisons

with 4 comments

SQL tidbits are always valuable and highly searched for by newbies (as opposed to reading the SQL documentation). Sometimes we seasoned SQL developers take for granted little things like when a single- or multiple-character wildcard comparison works. It seems we know what newbies don’t. That you need a wildcard comparison operator not simply and equality comparison operator.

The question posed to me was, “Why doesn’t my wildcard comparison work?” Here’s a simplified example of their question.

SELECT 'Valid' AS "Test"
FROM    dual
WHERE  'Treat' = 'Tre_t'
OR     'Treet' = 'Tre_t';

Naturally, the answer is that the equality operator compares the strings based on their exact match (character sensitively in Oracle and character insensitively in MySQL). It needs to be rewritten by replacing the equals (=) comparison operator with the LIKE comparison operator. The following query does that:

SELECT 'Valid' AS "Test"
FROM    dual
WHERE  'Treat' LIKE 'Tre_t'
OR     'Treet' LIKE 'Tre_t'

The same behavior exists for the multiple-character wildcard (%). I hope this helps those looking for this answer.

Written by maclochlainn

July 13th, 2013 at 1:12 pm

Finding DBMS_TYPES value?

without comments

Somebody asked me why they can’t query the DBMS_TYPES.TYPECODE_OBJECT value because they get an ORA-06553 error. Their query attempt is:

SELECT   dbms_types.typecode_object
FROM     dual;

Naturally, it raises the following exception:

SELECT   dbms_types.typecode_object
         *
ERROR at line 1:
ORA-06553: PLS-221: 'TYPECODE_OBJECT' IS NOT a PROCEDURE OR IS undefined

The explanation is very simple. It’s a package scoped variable and in Oracle 11g only accessible in a PL/SQL block. Here’s an anonymous block that would print the value to the console:

BEGIN
  dbms_output.put_line(dbms_types.typecode_object);
END;
/

Hope that helps those trying to discover what a package variable’s value is.

Written by maclochlainn

April 13th, 2013 at 12:31 pm

SQL Injection Risks

with 10 comments

While I tried to deflect how you perform SQL Injection attacks against a MySQL procedure, my students requested that I post examples of what to do to avoid SQL injection, and what not to do to invite attacks. The best practice to avoid SQL injection attacks is too always bind inputs to data types, and avoid providing completely dynamic WHERE clauses.

Here’s the correct way to dynamically generate a result from a MySQL Stored Procedure:

CREATE PROCEDURE hello (IN pv_input VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  WHERE  sample_name = pv_input;
END;
$$

A call to this hello procedure will only return the row or rows where the pv_input value matches the sample_name column value. Any attempt to exploit it like the one below fails.

CALL hello('\'Harriet\' OR 1 = 1');

It fails because there’s no matching 'Harriet' OR 1 = 1 in the table’s sample_name column. However, it works well when we submit 'Harriet' by herself, without the intended SQL inject phrase “OR 1 = 1“, as you can see:

+-----------+-------------+
| sample_id | sample_name |
+-----------+-------------+
|         2 | Harriet     |
+-----------+-------------+
1 row in set (0.00 sec)

There are two approaches that you should never put in your code because they can be readily exploited unless you carefully parse the incoming string parameter. The problem in both cases is causes by concatenation rather than binding. The first example is extremely unlikely as an error but possible.

CREATE PROCEDURE hello (IN pv_input VARCHAR(50))
BEGIN
  SET @sql := CONCAT('SELECT sample_id, sample_name FROM sample WHERE sample_name = ',pv_input);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
END;
$$

The last hello procedure using concatenation and a prepared statement is vulnerable to SQL injection. A call like the following would return all two rows in the sample table.

CALL hello('\'Juliette\' OR 1 = 1');

It would display:

+-----------+-------------+
| sample_id | sample_name |
+-----------+-------------+
|         1 | Hank        |
|         2 | Harriet     |
+-----------+-------------+
2 rows in set (0.00 sec)

While there’s no sample_name value of 'Juliette', the “OR 1 = 1” is true. Therefore, the SELECT statement filters out nothing and returns all the data from the table. It’s probably clear you’d never do this type of prepared statement inside a stored procedure, but most SQL Injection attacks exploit your scripting language implementation. Unfortunately, bad coding practices can infrequently expose this type of vulnerability; and they typically occur when a junior programmers is following a bad coding example.

A solution with the WHERE clause as part of the parameter would look like this:

CREATE PROCEDURE hello (IN pv_where VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  pv_where;
END;
$$

The modified call:

CALL hello('WHERE sample_name = \'Juliette\' OR 1 = 1');

returns all rows from the table.

A solution without the WHERE clause as part of the parameter would look like the following but it fails during runtime and returns no rows [Updated in response to comment]. The failure has nothing to do with the comment’s dialog on the CONCAT function, which also added nothing to the example once I tested it.

CREATE PROCEDURE hello (IN pv_where VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  WHERE  pv_where;
END;
$$

It’s simply works only when you provide a “1 = 1″ or other comparison without embedded apostrophes (‘) but fails with embedded apostrophes. That means the following statement fails:

CALL hello('sample_name = \'Juliette\' OR 1 = 1');

but this SQL injection statement works:

CALL hello('1 = 1');

returns all rows from the table.

This example, when you omit the white space also works with embedded strings or numeric operands and an operator:

CREATE PROCEDURE hello (IN pv_where VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  WHEREpv_where;
END;
$$

It returns all rows with a call like this:

CALL hello('sample_name = \'Juliette\' OR 1 = 1');

My take initially was that it might be a bug, and I logged one (Bug 68903). That’s was a dumb thing to do because WHEREpv_where simply becomes a table alias in the query.

In conclusion, the first example is a good practice. The other two should never exist! Well, they shouldn’t exist unless you’re parsing the web form inputs vigilantly.

Hope this helps those trying to understand how to avoid SQL injection attacks. Always try to solve dynamic SQL statement problems by binding variables into statements.

Written by maclochlainn

April 5th, 2013 at 8:35 pm

Conflict between identifiers

with one comment

Sometimes interesting problems lead to shock or dismay at the suppositions of why they occur. Why an ORA-22979 is raised is one of those, and the error is typically:

ERROR at line 1:
ORA-22979: cannot INSERT object VIEW REF OR user-defined REF

This error occurs on an INSERT statement if you follow the example from the Oracle 11gR2 Object-Relational Developer’s Guide, which also has various slightly modified examples in a couple PL/SQL books. It also happens on an UPDATE statement to populate REF values.

The conflict is typically between the uniqueness of the reference and an attempt to make a non-reference column of the object type a primary key constrained column and embedded object view. The source of the conflict is the OBJECT IDENTIFIER IS PRIMARY KEY associated with a primary key in the Oracle documentation. The two goals are mutually exclusive; only the reference or non-reference column can be the object identifier. Unfortunately, Oracle documentation contains both examples in different places without making any effective cross reference.

If you want to make a column of an object type a primary key for an object table (that is a table that uses an object type to define its structure) and the object view (the content of the embedded object type), you can’t include the OBJECT IDENTIFIER IS PRIMARY KEY clause when you want to populate the REF column of the object type. Here’s an example that uses a column of the object type as a primary key and leaves the REF column empty:

-- Create the BASE_T type, or specification for IDL.
CREATE OR REPLACE
  TYPE base_t IS OBJECT
  ( obj_id    NUMBER
  , obj_name  VARCHAR2(30)
  , obj_ref   REF base_t)
  NOT FINAL;
/

You can then create a table like the following:

CREATE TABLE base OF base_t
( obj_id CONSTRAINT base_pk PRIMARY KEY )
  OBJECT IDENTIFIER IS PRIMARY KEY;

Let’s insert some rows to test for ourselves that this fails when you try to assign references:

INSERT INTO base VALUES (base_t(1, 'Dwalin',NULL));
INSERT INTO base VALUES (base_t(2, 'Borfur',NULL));
INSERT INTO base VALUES (base_t(3, 'Gloin',NULL));
INSERT INTO base VALUES (base_t(4, 'Kili',NULL));
INSERT INTO base VALUES (base_t(5, 'Fili',NULL));

The following UPDATE statement attempts to assign references, but fails as shown below:

UPDATE   base b
SET      obj_ref = REF(b);

The UPDATE fails as shown:

UPDATE   base b
         *
ERROR at line 1:
ORA-22979: cannot INSERT object VIEW REF OR user-defined REF

The simple fix redefines the object table by removing the OBJ_ID column as an object identifier and primary key value. You do that by removing the OBJECT IDENTIFIER IS PRIMARY KEY clause because the column of the object type can be a primary key for the table without being an object view identifier. After you make the change, you can successfully update the table with object references. Object identifiers or references are unique and serve the same purpose of a primary key for the object view, and at the same time they can’t both exist.

CREATE TABLE base OF base_t

Inserting the same rows, you can now update the table to provide valid object references. Let’s experiment with how they work because that’s also not as clear as I’d like in the Oracle documentation.

The next statement creates a CHILD table that holds a reference to the BASE (or parent) table and another instance of the same BASE_T object type:

CREATE TABLE child
( child_id   NUMBER  CONSTRAINT child_pk PRIMARY KEY
, base_ref   REF base_t SCOPE IS base
, child      base_t);

The INSERT statement can’t use a VALUES clause because we MUST capture the reference (or in this case primary key) from the BASE (or parent) table. An INSERT statement with a query does the trick:

INSERT INTO child 
SELECT 1, obj_ref, base_t(1, 'Gimli',NULL)
FROM   base b
WHERE  b.obj_name = 'Gloin';

You should note that the reference for the CHILD table’s CHILD column isn’t set but is likewise not required for the example to work.

Now, lets perform an standard INNER JOIN (equijoin) between the two tables by using the references as primary and foreign keys. Please note the trick is referring to the table and column of the BASE (or parent) table and the table, column, and embedded OBJ_REF of the CHILD table.

COLUMN father FORMAT A10
COLUMN son    FORMAT A10
SELECT   b.obj_name AS "Father"
,        c.child.obj_name AS "Son"
FROM     base b INNER JOIN child c ON b.obj_ref = c.base_ref.obj_ref;

You get the following results:

Father     Son
---------- ----------
Gloin      Gimli

You can make a view of this table with either of these syntaxes:

CREATE OR REPLACE VIEW base_v OF base_t WITH OBJECT OID DEFAULT AS
SELECT * FROM base;

or,

CREATE OR REPLACE VIEW base_v OF base_t WITH OBJECT OID (obj_id) AS
SELECT * FROM base;

Hope it helps anybody trying it. Personally, I think it’s better to use collections of object types, but that’s much bigger discussion that I’ll save for the Oracle Database 12c PL/SQL Programming book that I’m writing.

Written by maclochlainn

March 30th, 2013 at 11:34 pm

Object Table Function View

with 2 comments

Somebody was trying to create a striped view based on a table’s start_date and end_date temporal columns. They asked for some help, so here are the steps (a two-minute tech-tip).

Basically, you create a user-defined data type, or structure:

1
2
3
4
CREATE OR REPLACE TYPE item_structure IS OBJECT
( id      NUMBER
, lookup  VARCHAR2(30));
/

Then, you create a list (an Oracle table) of the structure, like:

1
2
CREATE OR REPLACE TYPE item_lookup IS TABLE OF item_structure;
/

Lastly, you create an object table function, like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
CREATE OR REPLACE FUNCTION get_item_types RETURN item_lookup IS
 
  -- Declare a variable that uses the record structure.
  lv_counter      PLS_INTEGER := 1;
 
  -- Declare a variable that uses the record structure.
  lv_lookup_table  ITEM_LOOKUP := item_lookup();
 
  -- Declare static cursor structure.
  CURSOR c IS
    SELECT   cl.common_lookup_id AS lookup_id
    ,        SUBSTR(cl.common_lookup_meaning,1,60) AS lookup_meaning
    FROM     common_lookup cl
    WHERE    cl.common_lookup_table = 'ITEM'
    AND      cl.common_lookup_column = 'ITEM_TYPE'
    AND      SYSDATE BETWEEN cl.start_date AND NVL(cl.end_date,TRUNC(SYSDATE) + 1)
    ORDER BY cl.common_lookup_meaning;
 
BEGIN
 
  FOR i IN c LOOP
    lv_lookup_table.EXTEND;
 
    /* The assignment pattern for a SQL collection is incompatible with
       the cursor return type, and you must construct an instance of the
       object type before assigning it to a collection. */
    lv_lookup_table(lv_counter) := item_structure( i.lookup_id
                                                 , i.lookup_meaning );
 
    lv_counter := lv_counter + 1;
  END LOOP;
 
  /* Call an autonomous function or procedure here! It would allow you to
     capture who queried what and when; and acts like a pseudo trigger for
     queries. */
 
  RETURN lv_lookup_table;
END;
/

Now you can embed the object table function in a view, like this:

1
2
3
CREATE OR REPLACE VIEW item_lookup_view AS
  SELECT *
  FROM   TABLE(get_item_types);

Why not simply use an embedded query in the view, like the following?

SQL> CREATE OR REPLACE VIEW normal_view AS
  2    SELECT   cl.common_lookup_id AS lookup_id
  3      ,        SUBSTR(cl.common_lookup_meaning,1,60) AS lookup_meaning
  4      FROM     common_lookup cl
  5      WHERE    cl.common_lookup_table = 'ITEM'
  6      AND      cl.common_lookup_column = 'ITEM_TYPE'
  7      AND      SYSDATE BETWEEN cl.start_date AND NVL(cl.end_date,TRUNC(SYSDATE) + 1)
  8      ORDER BY cl.common_lookup_meaning;

My guess is that it was too easy but who knows, maybe they found a real need. The only need that I see occurs when you’re enforcing something like HIPPA and you want to capture unauthorized queries along with who performed them.

Naturally, I hope this helps those looking to resolve syntax errors when they have a need to do the more complex solution.

Written by maclochlainn

March 17th, 2013 at 10:59 pm

MySQL Auto Increment

with one comment

Somebody ran into a problem after reading about the MySQL CREATE statement and the AUTO_INCREMENT option. They couldn’t get a CREATE statement to work with an AUTO_INCREMENT value other than the default of 1. The problem was they were using this incorrect syntax:

CREATE TABLE elvira
( elvira_id    int unsigned PRIMARY KEY AUTO_INCREMENT=1001
, movie_title  varchar(60))
  ENGINE=InnoDB
  CHARSET=utf8;

It raises this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1001
, movie_title  varchar(60))
  ENGINE=InnoDB
  CHARSET=utf8' at line 2

They concluded that MySQL requires you to assign a default value of 1 as the initial automatic numbering value; then you use the ALTER statement to change the initial sequence value. That assumption is incorrect. The problem was with their assignment of an overriding AUTO_INCREMENT value inside the parenthetical list of columns. That assignment needs to occur after the list of columns and constraints, like

CREATE TABLE elvira
( elvira_id    int unsigned PRIMARY KEY AUTO_INCREMENT
, movie_title  varchar(60))
  ENGINE=InnoDB
  AUTO_INCREMENT=1001
  CHARSET=utf8;

It’s not an unlikely mistake since there’s no clear example on either of the referenced web pages (at the time of writing). It would be nice if they were added but I’m of the opinion some of the reference manual pages are too sparse.

After creating the table, you have the generic fix that seems to appear most often as an answer to setting or re-setting the auto numbering sequence of a MySQL table:

ALTER TABLE elvira AUTO_INCREMENT=1001;

Why would you set the auto numbering sequence to something other than 1? Some designers consider it a best practice to increment from a set point like 101 or 1,001 for your Application Programming Interface (API) and they leave a readily identifiable sequence number set for pre- or post-seeded data in tables. The region of preallocated numbers are typically only used in a few of the tables, in any application, but consistently managing sequences across all tables does sometimes simplifies data diagnostics.

Hope this helps somebody looking for a syntax fix. By the way, you can find it on Page 162 of the Oracle Database 11g & MySQL 5.6 Developer Handbook.

Written by maclochlainn

January 29th, 2013 at 1:10 am

Posted in MySQL,MySQL Workbench,sql

Tagged with ,

Placement over substance

with 5 comments

I was stunned when a SQL query raised an ERROR 1630 (42000) telling me the SUM function didn’t exist in MySQL 5.5.23. The fix was simple. The opening parenthesis of the SUM function must be on the same line as the SUM keyword without an intervening white space. Alternatively phrased, you can’t have a line return or white space between the SUM function name and the opening parenthesis of the call parameter list. The same rule doesn’t apply to the opening parenthesis of the FORMAT function and it seems to me that this parsing inconsistency is problematic.

Therefore, my surprise, observation, and complaint is that all functions don’t parse the same way, using the same rules. That is, unless you use specialized SQL_MODE settings. This assumption was borne out by Kolbe Kegel’s comment on this post, and there are 30 remaining built in functions that have specialized parsing and resolution markers.

A simplified version of the code that raises the error follows. As you’ll notice the opening parenthesis for the FORMAT and SUM function have intervening white space and a line return.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT   t.transaction_account AS "Transaction"
,        LPAD(FORMAT
           (SUM
             (CASE
                WHEN EXTRACT(MONTH FROM transaction_date) = 1 AND
                     EXTRACT(YEAR FROM transaction_date) = 2011 THEN
                  CASE
                    WHEN t.transaction_type = cl.common_lookup_type THEN
                      t.transaction_amount
                    ELSE
                      t.transaction_amount * -1
                  END
             END),2),10,' ') AS "JAN"
FROM     TRANSACTION t CROSS JOIN common_lookup cl
WHERE    cl.common_lookup_table = 'TRANSACTION'
AND      cl.common_lookup_column = 'TRANSACTION_TYPE'
AND      cl.common_lookup_type = 'DEBIT'
GROUP BY t.transaction_account;

Based on the comments, the SQL_MODE is:

mysql> SELECT @@version, @@sql_mode;
+-----------+----------------------------------------------------------------+
| @@version | @@sql_mode                                                     |
+-----------+----------------------------------------------------------------+
| 5.5.23    | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------+----------------------------------------------------------------+
1 ROW IN SET (0.00 sec)

It raises the following error:

ERROR 1630 (42000): FUNCTION studentdb.SUM does NOT exist. CHECK the 'Function Name Parsing and Resolution' SECTION IN the Reference Manual

Moving ONLY the opening parenthesis to the end of the SUM keyword (or removing the line return and white space from between the SUM keyword and opening parenthesis) prevents the error but it would be more convenient if it supported both approaches. It seems odd that an intervening line return and white space for the SUM function raises an exception while the same intervening line return and white space doesn’t raise an exception for the FORMAT function. It strikes me the parser should support both or reject both. Here’s the fixed code that works without enabling the IGNORE_SPACE SQL Mode option.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT   t.transaction_account AS "Transaction"
,        LPAD(FORMAT
           (SUM(
              CASE
                WHEN EXTRACT(MONTH FROM transaction_date) = 1 AND
                     EXTRACT(YEAR FROM transaction_date) = 2011 THEN
                  CASE
                    WHEN t.transaction_type = cl.common_lookup_type THEN
                      t.transaction_amount
                    ELSE
                      t.transaction_amount * -1
                  END
             END),2),10,' ') AS "JAN"
FROM     TRANSACTION t CROSS JOIN common_lookup cl
WHERE    cl.common_lookup_table = 'TRANSACTION'
AND      cl.common_lookup_column = 'TRANSACTION_TYPE'
AND      cl.common_lookup_type = 'DEBIT'
GROUP BY t.transaction_account;

As noted by the comments, adding the IGNORE_SPACE to the SQL_MODE lets both queries work without moving the open parenthesis. You can do that in a session with the following syntax (which is covered in an older post):

SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',IGNORE_SPACE'));

Hope this helps folks…

Written by maclochlainn

July 3rd, 2012 at 4:10 pm