MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Windows OS’ Category

Node.js MySQL Error

without comments

While I blogged about how to setup Node.js and MySQL almost two years ago, it was interesting when a student ran into a problem. The student said they’d configured the environment but were unable to use Node.js to access MySQL.

The error is caused by this import statement:

const mysql = require('mysql')

The student got the following error, which simply says that they hadn’t installed the Node.js package for MySQL driver.

internal/modules/cjs/loader.js:638
    throw err;
    ^
 
Error: Cannot find module 'mysql'
    at Function.Module._resolveFilename (internal/modules/cjs/loader.js:636:15)
    at Function.Module._load (internal/modules/cjs/loader.js:562:25)
    at Module.require (internal/modules/cjs/loader.js:692:17)
    at require (internal/modules/cjs/helpers.js:25:18)
    at Object.<anonymous> (/home/student/Data/cit325/oracle-s/lib/Oracle12cPLSQLCode/Introduction/query.js:4:15)
    at Module._compile (internal/modules/cjs/loader.js:778:30)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:789:10)
    at Module.load (internal/modules/cjs/loader.js:653:32)
    at tryModuleLoad (internal/modules/cjs/loader.js:593:12)
    at Function.Module._load (internal/modules/cjs/loader.js:585:3)

I explained they could fix the problem with the following two Node.js Package Manager (NPM) commands:

npm init --y 
npm install --save mysql

The student was able to retest the code with success. The issue was simply that the Node.js couldn’t find the NPM MySQL module.

Written by maclochlainn

June 12th, 2022 at 1:58 pm

Logging Table Function

with one comment

It is interesting when somebody remembers a presentation from 10 years ago. They asked if it was possible in PL/pgSQL to write an autonomous procedure to log data when calling a table view function. The answer is two fold. PL/pgSQL doesn’t support autonomous functions or procedures like the Oracle database but it doesn’t need to because unless you invoke a transaction it auto commits writes.

Logging table functions are important for security auditing and compliance management against laws, like SOX, HIPAA, and FERPA. All too many systems lack the basic ability to audit who queries records without raising an error and blocking the access. That means the bad actor or actress gains the ability to probe the system for weaknesses before determining an attack vector. It’s often better to capture the unauthorized access and take direct action to protect both the the data and systems.

While the example lets an unauthorized person access the information in the first version of the student_query, it blocks access by reporting no rows returned in the latter. Both versions of the query log the data and thereby collect the evidence necessary to act against the hack.

This blog post shows you how to write it and test it. Follow the following steps:

  1. Create the necessary tables and data to work with a logging PL/pgSQL table view function:

    /* Conditionally drop and create table. */
    DROP TABLE IF EXISTS student;
    CREATE TABLE student
    ( student_id      SERIAL
    , first_name      VARCHAR(20)
    , last_name       VARCHAR(20)
    , hogwarts_house  VARCHAR(10));
     
    /* Conditionally drop and create table. */
    DROP TABLE IF EXISTS logger;
    CREATE TABLE logger
    ( logger_id        SERIAL
    , app_user         VARCHAR(30)
    , queried_student  VARCHAR(30)
    , query_time       TIMESTAMP );
     
    /* Insert one record into table. */
    INSERT INTO student
    ( first_name, last_name, hogwarts_house )
    VALUES
     ( 'Harry', 'Potter', 'Gryffindor' )
    ,( 'Hermione', 'Granger', 'Gryffindor' )
    ,( 'Ronald', 'Weasily', 'Gryffindor' )
    ,( 'Draco', 'Malfoy', 'Slytherin' )
    ,( 'Vincent', 'Crabbe', 'Slytherin' )
    ,( 'Susan', 'Bones', 'Hufflepuff' )
    ,( 'Hannah', 'Abbott', 'Hufflepuff' )
    ,( 'Luna', 'Lovegood', 'Ravenclaw' )
    ,( 'Cho', 'Chang', 'Ravenclaw' )
    ,( 'Gilderoy', 'Lockhart', 'Ravenclaw' );
  2. While not necessary if you’re very familiar with PL/pgSQL, it may be helpful to review:

    • The SET command that lets you assign a value to a session-level variable, which you can later use in a PL/pgSQL block.
    • The SELECT-INTO statement in a DO-block.

    Here’s a test script that demonstrates both:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    
    /* Set a session-level variable. */
    SET credential.app_user = 'Draco Malfoy';
     
    /* Secure the value from a session-level variable. */
    SELECT current_setting('credential.app_user');
     
    /* 
    DO
    $$
    DECLARE
      input   VARCHAR(30) := 'Hermione';
      output  VARCHAR(30);
    BEGIN
      /* Sample for partial name construction of full name. */
      SELECT CONCAT(s.first_name, ' ', s.last_name) AS student_name
      INTO   output
      FROM   student s
      WHERE  CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||input||'%';
     
      /* Show result of local assignment via a query. */
      RAISE NOTICE '[%][%]', current_setting('credential.app_user'), output;
    END;
    $$;

    There’s an important parsing trick to this sample program. It uses the LIKE operator rather than the SIMILAR TO operator because the parser fails to recognize the SIMILAR TO operator.

    The DO-block returns the following output:

    NOTICE:  [Draco Malfoy][Hermione Granger]
  3. This creates the student_query logging table function, which takes a partial portion of a students first and last name to return the student information. While the example only returns the name and the Hogwarts House it lays a foundation for a more complete solution.

    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
    
    CREATE OR REPLACE
      FUNCTION student_query (partial_name  VARCHAR)
      RETURNS TABLE ( first_naem      VARCHAR(20)
                    , last_name       VARCHAR(20)
                    , hogwarts_house  VARCHAR(10) ) AS
    $$
    DECLARE
      queried   VARCHAR;
      by_whome  VARCHAR;
    BEGIN
      /* Query separately because embedding in insert statement fails. */
      SELECT CONCAT(s.first_name, ' ', s.last_name) AS student_name
      FROM   student s INTO queried
      WHERE  CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||partial_name||'%';
     
      /* Log the query with the credentials of the user. */  
      INSERT INTO logger
      ( app_user
      , queried_student
      , query_time )
      VALUES
      ( current_setting('credential.app_user')
      , queried
      , NOW());
     
      /* Return the result set without disclosing the query was recorded. */
      RETURN QUERY
      SELECT s.first_name
      ,      s.last_name
      ,      s.hogwarts_house
      FROM   student s
      WHERE  CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||partial_name||'%';
    END;
    $$ LANGUAGE plpgsql;
  4. You can test the function by calling it, like this:

    SELECT * FROM student_query('Hermione');

    It displays:

     first_naem | last_name | hogwarts_house
    ------------+-----------+----------------
     Hermione   | Granger   | Gryffindor
    (1 row)

    You can check the logging table and discover who looked up another student’s records.

    SELECT * FROM logger;

    It displays:

     logger_id |   app_user   | queried_student  |         query_time
    -----------+--------------+------------------+----------------------------
             1 | Draco Malfoy | Hermione Granger | 2022-05-29 22:51:50.398987
    (1 row)
  5. Assuming you’ve built an authorized_user function that returns a Boolean, you can add a call to it in the WHERE clause. For simplicity, let’s implement the function to deny all users, like:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    
    CREATE OR REPLACE
      FUNCTION authorized_user
      (user_name  VARCHAR) RETURNS BOOLEAN AS
    $$
    DECLARE
      lv_retval  BOOLEAN := FALSE;
    BEGIN
      RETURN lv_retval;
    END;
    $$  LANGUAGE plpgsql;

    You can now replace the query on lines 28 through 32 with the new one below. The added clause on line 33 denies access to unauthorized users because there aren’t any.

    28
    29
    30
    31
    32
    33
    
      SELECT s.first_name
      ,      s.last_name
      ,      s.hogwarts_house
      FROM   student s
      WHERE  CONCAT(s.first_name, ' ', s.last_name) LIKE '%'||partial_name||'%'
      AND    authorized_user(current_setting('credential.app_user'));

    While it returns:

     first_naem | last_name | hogwarts_house
    ------------+-----------+----------------
    (0 rows)

    The logger table shows two entries. One for the query that returned a value and one for the version that didn’t.

     logger_id |   app_user   | queried_student  |         query_time
    -----------+--------------+------------------+----------------------------
             1 | Draco Malfoy | Hermione Granger | 2022-05-29 23:23:39.82063
             2 | Draco Malfoy | Hermione Granger | 2022-05-29 23:23:40.736945
    (2 rows)

    In both cases the bad actor Draco Malfoy’s unauthorized access is captured and he was denied any information without alerting him to the security precaution in a logging table function.

As always, I hope this helps those looking for this type of solution.

PL/pgSQL List to Struct

without comments

This blog post addresses how to convert a list of values into a structure (in C/C++ its a struct, in Java its an ArrayList, and PL/pgSQL it’s an array of a type). The cast_strings function converts a list of strings into a record data structure. It calls the verify_date function to identify a DATE data type and uses regular expressions to identify numbers and strings.

You need to build the struct type below first.

CREATE TYPE struct AS
( xnumber  DECIMAL
, xdate    DATE
, xstring  VARCHAR(100));

The cast_strings function is defined below:

CREATE FUNCTION cast_strings
( pv_list  VARCHAR(10)[] ) RETURNS struct AS
  $$
  DECLARE
  /* Declare a UDT and initialize an empty struct variable. */
  lv_retval  STRUCT := (null, null, null); 
  BEGIN  
    /* Loop through list of values to find only the numbers. */
    FOR i IN 1..ARRAY_LENGTH(pv_list,1) LOOP
      /* Order if statements by evaluation. */
      CASE
        /* Check for a value with only digits. */
        WHEN lv_retval.xnumber IS NULL AND REGEXP_MATCH(pv_list[i],'^[0-9]+$') IS NOT NULL THEN
          lv_retval.xnumber := pv_list[i];
        /* Check for a valid date. */
        WHEN lv_retval.xdate IS NULL AND verify_date(pv_list[i]) IS NOT NULL THEN
          lv_retval.xdate := pv_list[i];
        /* Check for a string with characters, whitespace, and digits. */
        WHEN lv_retval.xstring IS NULL AND REGEXP_MATCH(pv_list[i],'^[A-Za-z 0-9]+$') IS NOT NULL THEN
          lv_retval.xstring := pv_list[i];
        ELSE
          NULL;
      END CASE;
    END LOOP;
 
    /* Print the results. */
    RETURN lv_retval;
  END;
$$ LANGUAGE plpgsql;

There are two test cases for the cast_strings function. One uses a DO-block and the other a query.

  • The first use-case checks with a DO-block:

    DO
    $$
    DECLARE
      lv_list    VARCHAR(11)[] := ARRAY['86','1944-04-25','Happy'];
      lv_struct  STRUCT;
    BEGIN
      /* Pass the array of strings and return a record type. */
      lv_struct := cast_strings(lv_list);
     
      /* Print the elements returned. */
      RAISE NOTICE '[%]', lv_struct.xnumber;
      RAISE NOTICE '[%]', lv_struct.xdate;
      RAISE NOTICE '[%]', lv_struct.xstring;
    END;
    $$;

    It should return:

    psql:verify_pg.SQL:263: NOTICE:  [86]
    psql:verify_pg.SQL:263: NOTICE:  [1944-04-25]
    psql:verify_pg.SQL:263: NOTICE:  [Happy]

    The program returns a structure with values converted into their appropriate data type.

  • The second use-case checks with a query:

    WITH get_struct AS
    (SELECT cast_strings(ARRAY['99','2015-06-14','Agent 99']) AS mystruct)
    SELECT (mystruct).xnumber
    ,      (mystruct).xdate
    ,      (mystruct).xstring
    FROM    get_struct;

    It should return:

     xnumber |   xdate    | xstring
    ---------+------------+----------
          99 | 2015-06-14 | Agent 99
    (1 row)

    The query defines a call to the cast_strings function with a valid set of values and then displays the elements of the returned structure.

As always, I hope this helps those looking for how to solve this type of problem. Just a quick reminder that this was written and tested in PostgreSQL 14.

PL/pgSQL Date Function

with 2 comments

This post provides an example of using PostgreSQL’s REGEXP_MATCH function, which works very much like the REGEXP_LIKE function in Oracle and a verify_date function that converts a string data type to date data type.

Here’s a basic function to show how to use a generic REGEXP_MATCH function:

1
2
3
4
5
6
7
8
9
10
11
DO
$$
DECLARE
  lv_date_in  DATE := '2022-10-22';
BEGIN
 
  IF (REGEXP_MATCH('2022-10-02','^[0-9]{4,4}-[0-9]{2,2}-[0-9]{2,2}$') IS NOT NULL) THEN
    RAISE NOTICE '[%]', 'Truth';
  END IF;
END;
$$;

The following is a verify_date function, which takes a string with the ‘YYYY-MM-DD’ or ‘YY-MM-DD’ format and returns a BOOLEAN true or false value.

CREATE FUNCTION verify_date
  ( IN pv_date_in  VARCHAR(10)) RETURNS BOOLEAN AS
  $$
  DECLARE
    /* Local return variable. */
    lv_retval  BOOLEAN := FALSE;
  BEGIN
    /* Check for a YYYY-MM-DD or YYYY-MM-DD string. */
    IF REGEXP_MATCH(pv_date_in,'^[0-9]{2,4}-[0-9]{2,2}-[0-9]{2,2}$') IS NOT NULL THEN
 
      /* Case statement checks for 28 or 29, 30, or 31 day month. */
      CASE
        /* Valid 31 day month date value. */
        WHEN (LENGTH(pv_date_in) = 10 AND
              SUBSTRING(pv_date_in,6,2) IN ('01','03','05','07','08','10','12') AND
              TO_NUMBER(SUBSTRING(pv_date_in,9,2),'99') BETWEEN 1 AND 31) OR
             (LENGTH(pv_date_in) = 8 AND
              SUBSTRING(pv_date_in,4,2) IN ('01','03','05','07','08','10','12') AND
              TO_NUMBER(SUBSTRING(pv_date_in,7,2),'99') BETWEEN 1 AND 31) THEN 
          lv_retval := TRUE;
 
        /* Valid 30 day month date value. */
        WHEN (LENGTH(pv_date_in) = 10 AND
              SUBSTRING(pv_date_in,6,2) IN ('04','06','09','11') AND
              TO_NUMBER(SUBSTRING(pv_date_in,9,2),'99') BETWEEN 1 AND 30) OR
             (LENGTH(pv_date_in) = 8 AND
              SUBSTRING(pv_date_in,4,2) IN ('04','06','09','11') AND
              TO_NUMBER(SUBSTRING(pv_date_in,7,2),'99') BETWEEN 1 AND 30) THEN 
          lv_retval := TRUE;
 
        /* Valid 28 or 29 day month date value. */
        WHEN (LENGTH(pv_date_in) = 10 AND SUBSTRING(pv_date_in,6,2) = '02') OR
             (LENGTH(pv_date_in) =  8 AND SUBSTRING(pv_date_in,4,2) = '02') THEN
          /* Verify 4-digit year. */
          IF (LENGTH(pv_date_in) = 10 AND
              MOD(TO_NUMBER(SUBSTRING(pv_date_in,1,4),'99'),4) = 0 AND
              TO_NUMBER(SUBSTRING(pv_date_in,9,2),'99') BETWEEN 1 AND 29) OR
             (LENGTH(pv_date_in) =  8 AND
              MOD(TO_NUMBER(SUBSTRING(TO_CHAR(TO_DATE(pv_date_in,'YYYY-MM-DD'),'YYYY-MM-DD'),1,4),'99'),4) = 0 AND
              TO_NUMBER(SUBSTRING(pv_date_in,7,2),'99') BETWEEN 1 AND 29) THEN
            lv_retval := TRUE;
          ELSE /* Not a leap year. */
            IF (LENGTH(pv_date_in) = 10 AND
                TO_NUMBER(SUBSTRING(pv_date_in,9,2),'99') BETWEEN 1 AND 28) OR
               (LENGTH(pv_date_in) = 8 AND
                TO_NUMBER(SUBSTRING(pv_date_in,7,2),'99') BETWEEN 1 AND 28)THEN
              lv_retval := TRUE;
            END IF;
          END IF;
       NULL;
      END CASE;
    END IF;
 
    /* Return date. */
    RETURN lv_retval;
  END;
$$ LANGUAGE plpgsql;

The following four SQL test cases:

SELECT verify_date('2020-07-04') AS "verify_date('2020-07-04')";
SELECT verify_date('71-05-31')   AS "verify_date('71-05-31')";
SELECT verify_date('2024-02-29') AS "verify_date('2024-02-29')";
SELECT verify_date('2019-04-31') AS "verify_date('2019-04-31')";

Return the following:

 verify_date('2020-07-04')
---------------------------
 t
(1 row)
 
 
 verify_date('71-05-31')
-------------------------
 t
(1 row)
 
 
 verify_date('2024-02-29')
---------------------------
 t
(1 row)
 
 
 verify_date('2019-04-31')
---------------------------
 f
(1 row)

As always, I hope the example code fills somebody’s need.

Written by maclochlainn

May 25th, 2022 at 1:47 am

PL/SQL List to Struct

without comments

Every now and then, I get questions from folks about how to tune in-memory elements of their PL/SQL programs. This blog post address one of those core issues that some PL/SQL programmers avoid.

Specifically, it addresses how to convert a list of values into a structure (in C/C++ its a struct, in Java its an ArrayList, and PL/SQL it’s a table of scalar or object types). Oracle lingo hides the similarity by calling either an Attribute Definition Type (ADT) or User-Defined Type (UDT). The difference in the Oracle space is that an ADT deals with a type defined in DBMS_STANDARD package, which is more or less like a primitive type in Java.

Oracle does this for two reasons:

The cast_strings function converts a list of strings into a record data structure. It lets the list of strings have either a densely or sparsely populated list of values, and it calls the verify_date function to identify a DATE data type and regular expressions to identify numbers and strings.

You need to build a UDT object type and lists of both ADT and UDT data types.

/* Create a table of strings. */
CREATE OR REPLACE
  TYPE tre AS TABLE OF VARCHAR2(20);
/
 
/* Create a structure of a date, number, and string. */
CREATE OR REPLACE
  TYPE struct IS OBJECT
  ( xdate     DATE
  , xnumber  NUMBER
  , xstring  VARCHAR2(20));
/
 
/* Create a table of tre type. */
CREATE OR REPLACE
  TYPE structs IS TABLE OF struct;
/

The cast_strings function is defined below:

CREATE OR REPLACE
  FUNCTION cast_strings
  ( pv_list  TRE ) RETURN struct IS
 
  /* Declare a UDT and initialize an empty struct variable. */
  lv_retval  STRUCT := struct( xdate => NULL
                             , xnumber => NULL
					         , xstring => NULL); 
  BEGIN  
    /* Loop through list of values to find only the numbers. */
    FOR i IN 1..pv_list.LAST LOOP
      /* Ensure that a sparsely populated list can't fail. */
      IF pv_list.EXISTS(i) THEN
        /* Order if number evaluation before string evaluation. */
        CASE
          WHEN lv_retval.xnumber IS NULL AND REGEXP_LIKE(pv_list(i),'^[[:digit:]]*$') THEN
            lv_retval.xnumber := pv_list(i);
          WHEN verify_date(pv_list(i)) THEN
            IF lv_retval.xdate IS NULL THEN
              lv_retval.xdate := pv_list(i);
            ELSE
              lv_retval.xdate := NULL;
            END IF;
          WHEN lv_retval.xstring IS NULL AND REGEXP_LIKE(pv_list(i),'^[[:alnum:]]*$') THEN
            lv_retval.xstring := pv_list(i);
          ELSE
            NULL;
        END CASE;
      END IF;
    END LOOP;
 
    /* Print the results. */
    RETURN lv_retval;
  END;
/

There are three test cases for this function:

  • The first use-case checks whether the input parameter is a sparsely or densely populated list:

    DECLARE
      /* Declare an input variable of three or more elements. */
      lv_list    TRE := tre('Berlin','25','09-May-1945','45');
     
      /* Declare a variable to hold the compound type values. */
      lv_struct  STRUCT;
    BEGIN
      /* Make the set sparsely populated. */
      lv_list.DELETE(2);
     
      /* Test the cast_strings function. */
      lv_struct := cast_strings(lv_list);
     
      /* Print the values of the compound variable. */
      dbms_output.put_line(CHR(10));
      dbms_output.put_line('xstring ['||lv_struct.xstring||']');
      dbms_output.put_line('xdate   ['||TO_CHAR(lv_struct.xdate,'DD-MON-YYYY')||']');
      dbms_output.put_line('xnumber ['||lv_struct.xnumber||']');
    END;
    /

    It should return:

    xstring [Berlin]
    xdate   [09-MAY-1945]
    xnumber [45]

    The program defines two numbers and deletes the first number, which is why it prints the second number.

  • The second use-case checks with a list of only one element:

    SELECT TO_CHAR(xdate,'DD-MON-YYYY') AS xdate
    ,      xnumber
    ,      xstring
    FROM   TABLE(structs(cast_strings(tre('catch22','25','25-Nov-1945'))));

    It should return:

    XDATE                   XNUMBER XSTRING
    -------------------- ---------- --------------------
    25-NOV-1945                  25 catch22

    The program returns a structure with values converted into their appropriate data type.

  • The third use-case checks with a list of two elements:

    SELECT TO_CHAR(xdate,'DD-MON-YYYY') AS xdate
    ,      xnumber
    ,      xstring
    FROM   TABLE(structs(cast_strings(tre('catch22','25','25-Nov-1945'))
                        ,cast_strings(tre('31-APR-2017','1918','areodromes'))));

    It should return:

    XDATE                   XNUMBER XSTRING
    -------------------- ---------- --------------------
    25-NOV-1945                  25 catch22
                               1918 areodromes

    The program defines calls the cast_strings with a valid set of values and an invalid set of values. The invalid set of values contains a bad date in the set of values.

As always, I hope this helps those looking for how to solve this type of problem.

PL/SQL CASE Not Found

without comments

I was working on some test cases for my students and changing the behavior of a verify_date function that I wrote years ago to validate and returns valid dates when they’re passed as strings. The original program returned today’s date when the date was invalid.

The new function returns a BOOLEAN value of false by default and true when the string validates as a date. Unfortunately, I introduced a mistake that didn’t use to exist in Oracle 11g, which was the version when I wrote the original function.

The test cases in Oracle 21c raises the following error when an invalid date is passed to the CASE statement by the cast_strings function that calls the new verify_date function:

FROM   TABLE(structs(cast_strings(tre('31-APR-2017','1917','dirk'))))
                     *
ERROR AT line 2:
ORA-06592: CASE NOT found WHILE executing CASE statement
ORA-06512: AT "C##STUDENT.VERIFY_DATE", line 30
ORA-06512: AT "C##STUDENT.CAST_STRINGS", line 18

As you can see, the test case uses ’31-APR-2017′ as an incorrect date to verify the use-case. The error occurred because the ELSE clause in the CASE statement wasn’t provided. Previously, the ELSE clause was optional and setting the lv_retval return variable to FALSE in the DECLARE block made it unnecessary.

The fixed code follows:

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
CREATE OR REPLACE
  FUNCTION verify_date
  ( pv_date_in  VARCHAR2) RETURN BOOLEAN IS
 
  /* Local variable to ensure case-insensitive comparison. */
  lv_date_in  VARCHAR2(11);
 
  /* Local return variable. */
  lv_date  BOOLEAN := FALSE;
BEGIN
  /* Convert string input to uppercase month. */
  lv_date_in := UPPER(pv_date_in);
 
  /* Check for a DD-MON-RR or DD-MON-YYYY string. */
  IF REGEXP_LIKE(lv_date_in,'^[0-9]{2,2}-[ADFJMNOS][ACEOPU][BCGLNPRTVY]-([0-9]{2,2}|[0-9]{4,4})$') THEN
    /* Case statement checks for 28 or 29, 30, or 31 day month. */
    CASE
      /* Valid 31 day month date value. */
      WHEN SUBSTR(lv_date_in,4,3) IN ('JAN','MAR','MAY','JUL','AUG','OCT','DEC') AND
           TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 31 THEN 
        lv_date := TRUE;
      /* Valid 30 day month date value. */
      WHEN SUBSTR(lv_date_in,4,3) IN ('APR','JUN','SEP','NOV') AND
           TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 30 THEN 
        lv_date := TRUE;
      /* Valid 28 or 29 day month date value. */
      WHEN SUBSTR(lv_date_in,4,3) = 'FEB' THEN
        /* Verify 2-digit or 4-digit year. */
        IF (LENGTH(pv_date_in) = 9 AND MOD(TO_NUMBER(SUBSTR(pv_date_in,8,2)) + 2000,4) = 0 OR
            LENGTH(pv_date_in) = 11 AND MOD(TO_NUMBER(SUBSTR(pv_date_in,8,4)),4) = 0) AND
            TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 29 THEN
          lv_date := TRUE;
        ELSE /* Not a leap year. */
          IF TO_NUMBER(SUBSTR(pv_date_in,1,2)) BETWEEN 1 AND 28 THEN
            lv_date := TRUE;
          END IF;
        END IF;
      ELSE
        NULL;
    END CASE;
  END IF;
  /* Return date. */
  RETURN lv_date;
EXCEPTION
  WHEN VALUE_ERROR THEN
    RETURN lv_date;
END;
/

The new ELSE clause in on lines 31 and 32, and the converted function works. I also added a local lv_date_in variable to hold an uppercase version of an input string to: ensure a case-insensitive comparison of the month value, and avoid a having to pass the input as an IN OUT mode parameter. Typically, I leave off exception handlers because mistyping or copying for newer programmers becomes easier, but in this case I added an exception handler for strings that are larger than 11-characters.

As always, I hope this helps those looking for a solution to a coding problem.

Written by maclochlainn

May 22nd, 2022 at 5:41 pm

Oracle DSN Security

without comments

Oracle disallows entry of a password value when configuring the ODBC’s Windows Data Source Name (DSN) configurations. As you can see from the dialog’s options:

So, I check the Oracle ODBC’s property list with the following PowerShell command:

Get-Item -Path Registry::HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Oracle | Select-Object

It returned:

Oracle                         Driver                 : C:\app\mclaughlinm\product\18.0.0\dbhomeXE\BIN\SQORA32.DLL
                               DisableRULEHint        : T
                               Attributes             : W
                               SQLTranslateErrors     : F
                               LobPrefetchSize        : 8192
                               AggregateSQLType       : FLOAT
                               MaxTokenSize           : 8192
                               FetchBufferSize        : 64000
                               NumericSetting         : NLS
                               ForceWCHAR             : F
                               FailoverDelay          : 10
                               FailoverRetryCount     : 10
                               MetadataIdDefault      : F
                               BindAsFLOAT            : F
                               BindAsDATE             : F
                               CloseCursor            : F
                               EXECSchemaOpt          :
                               EXECSyntax             : F
                               Application Attributes : T
                               QueryTimeout           : T
                               CacheBufferSize        : 20
                               StatementCache         : F
                               ResultSets             : T
                               MaxLargeData           : 0
                               UseOCIDescribeAny      : F
                               Failover               : T
                               Lobs                   : T
                               DisableMTS             : T
                               DisableDPM             : F
                               BatchAutocommitMode    : IfAllSuccessful
                               Description            : Oracle ODBC
                               ServerName             : xe
                               Password               : 
                               UserID                 : c##student
                               DSN                    : Oracle

Then, I used this PowerShell command to set the Password property:

Set-ItemProperty -Path Registry::HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Oracle -Name "Password" -Value 'student'

After setting the Password property’s value, I queried it with the following PowerShell command:

Get-ItemProperty -Path Registry::HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Oracle | Select-Object -Property "Password"

It returns:

Password : student

After manually setting the Oracle ODBC DSN’s password value you can now connect without providing a password at runtime. It also means anybody who hacks the Windows environment can access the password through trivial PowerShell command.

I hope this alerts readers to a potential security risk when you use Oracle DSNs.

What’s up on M1 Chip?

with one comment

I’ve been trying to sort out what’s up on Oracle’s support of Apple’s M1 (arm64) chip. It really is a niche area. It only applies to those of us who work on a macOS machines with Oracle Database technology; and then only when we want to install a version of Oracle Database 21c or newer in Windows OS for testing. Since bootcamp is now gone, these are only virtualized solutions through a full virtualization product or containerized with Docker of Kubernetes.

The Best Virtual Machine Software for Mac 2022 (4/11/2022) article lets us know that only Parallels fully supports Windows virtualization on the ARM64 chip. Then, there’s the restriction that you must use Monterey or Big Sur (macOS) and Windows 11 arm64.

Instructions were published on On how to run Windows 11 on an Apple M1 a couple weeks ago. They use the free UTM App from the Apple Store and provide the download site for the Windows Insider Program. You can’t get a copy of Windows 11 arm64 without becoming part of the Windows Insider Program.

The next step would be to try and install Oracle Database 21c on Windows 11 arm64, which may or may not work. At least, I haven’t tested it yet and can’t make the promise that it works. After all, I doubt it will work because the Oracle Database 21c documentation says it only supports x64 (or Intel) architecture.

If anybody knows what Oracle has decided, will decide or even their current thinking on the issue, please make a comment.

Written by maclochlainn

May 1st, 2022 at 11:56 pm

MySQL RegExp Default

with 4 comments

We had an interesting set of questions regarding the REGEXP comparison operator in MySQL today in both sections of Database Design and Development. They wanted to know the default behavior.

For example, we built a little movie table so that we didn’t change their default sakila example database. The movie table was like this:

CREATE TABLE movie
( movie_id     int unsigned primary key auto_increment
, movie_title  varchar(60)) auto_increment=1001;

Then, I inserted the following rows:

INSERT INTO movie 
( movie_title )
VALUES
 ('The King and I')
,('I')
,('The I Inside')
,('I am Legend');

Querying all results with this query:

SELECT * FROM movie;

It returns the following results:

+----------+----------------+
| movie_id | movie_title    |
+----------+----------------+
|     1001 | The King and I |
|     1002 | I              |
|     1003 | The I Inside   |
|     1004 | I am Legend    |
+----------+----------------+
4 rows in set (0.00 sec)

The following REGEXP returns all the rows because it looks for a case insensitive “I” anywhere in the string.

SELECT movie_title
FROM   movie
WHERE  movie_title REGEXP 'I';

The implicit regular expression is actually:

WHERE  movie_title REGEXP '^.*I.*$';

It looks for zero-to-many of any character before and after the “I“. You can get any string beginning with an “I” with the “^I“; and any string ending with an “I” with the “I$“. Interestingly, the “I.+$” should only match strings with one or more characters after the “I“, but it returns:

+----------------+
| movie_title    |
+----------------+
| The King and I |
| The I Inside   |
| I am Legend    |
+----------------+
3 rows in set (0.00 sec)

This caught me by surprise because I was lazy. As pointed out in the comment, it only appears to substitute a “.*“, or zero-to-many evaluation for the “.+” because it’s a case-insensitive search. There’s another lowercase “i” in the “The King and I” and that means the regular expression returns true because that “i” has one-or-more following characters. If we convert it to a case-sensitive comparison with the keyword binary, it works as expected because it ignores the lowercase “i“.

WHERE  binary movie_title REGEXP '^.*I.*$';

This builds on my 10-year old post on Regular Expressions. As always, I hope these notes helps others discovering features and behaviors of the MySQL database, and Bill thanks for catching my error.

Written by maclochlainn

April 29th, 2022 at 11:50 pm

Multidimension Arrays

without comments

Picking up where I left off on yesterday’s post on PostgreSQL arrays, you can also write multidimensional arrays provided all the nested arrays are equal in size. You can’t use the CARDINALITY function to determine the length of nested arrays, you must use the ARRAY_LENGTH to determine the length of subordinate arrays.

Here’s an example file with a multidimensional array of integers:

DO
$$
DECLARE
  /* Declare an array of integers with a subordinate array of integers. */
  list  int[][] = array[array[1,2,3,4]
                       ,array[1,2,3,4]
                       ,array[1,2,3,4]
                       ,array[1,2,3,4]
                       ,array[1,2,3,4]];
  row   varchar(20) = '';
BEGIN
  /* Loop through the first dimension of integers. */
  <<Outer>>
  FOR i IN 1..ARRAY_LENGTH(list,1) LOOP
    row = '';
    /* Loop through the second dimension of integers. */
    <<Inner>>
    FOR j IN 1..ARRAY_LENGTH(list,2) LOOP
      IF LENGTH(row) = 0 THEN
        row = row || list[i][j];
      ELSE
        row = row || ',' || list[i][j];
      END IF;
    END LOOP;
    /* Exit outer loop. */
    RAISE NOTICE 'Row [%][%]', i, row;
  END LOOP;
END;
$$;

It prints:

NOTICE:  Row [1][1,2,3,4]
NOTICE:  Row [2][1,2,3,4]
NOTICE:  Row [3][1,2,3,4]
NOTICE:  Row [4][1,2,3,4]
NOTICE:  Row [5][1,2,3,4]
DO

Multidimensional arrays are unique to PostgreSQL but you can have nested lists of tables or varrays inside an Oracle database. Oracle also supports nested lists that are asynchronous.

As always, I hope this helps those trying sort out the syntax.