MacLochlainns Weblog

I actively maintain virtual test instances for my writing and teaching on Windows 7, Red Hat Enterprise Linux, Fedora, and Solaris. Perl on Windows is always interesting. I use ActivePerl on Windows 7 rather than Strawberry Perl, and it was interesting to see this note after I upgraded to the most current Community Edition of ActivePerl (5.16.3).

I thought it might be kind to post ActiveState’s Release Note because you should really read it before you try to install ActivePerl on Windows 7/8. Just make sure you’ve removed any earlier version of ActivePerl before trying the install. That’s what I did, and surprise, there weren’t any problems.

While the installation doesn’t tell you that you need to restart the Windows 7 operating system, you do. If you don’t restart Windows 7 after the ActivePerl install, you’ll get the following error message:

install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC

You can grab the DBD::MySQL bundle with this command:

perl -MCPAN -e install Bundle::DBD::mysql

It downloads the MinGW (Minimalist GNU for Windows) C/C++ compiler, but it’ll be nested and not conflict with one if you have installed it independently. Absence of the compiler means you can’t attempt to build the library.

Proceeding further, I discovered a number of incompatibilities. The basic commands require you to load CPAN (Comprehensive Perl Archive Network) shell:

perl -MCPAN -e shell

Inside the Perl CPAN shell:

cpan> force install DBD:mysql --mysql_config C:\ProgramData\MySQL\MySQL Server 5.6

You also need to create a MySQL anonymous user in the database to support the default user during compilation, and if you dropped the test database you need to recreate it. I also ended up creating an ODBC user. Here are the commands to run as the root superuser:

CREATE USER 'ODBC'@'localhost';
CREATE USER ''@'localhost' IDENTIFIED BY 's3krit';
GRANT ALL ON test.* TO ''@'localhost';

I also took the time to update the MySQL test.pl to Perl 5.16; unfortunately, at the end of the day I didn’t get it working. Maybe I’ll try back in a few weeks. You need to change line 57 from port 5050 to port 3306, and “SharedSecret” to “s3krit“, as shown:

Here’s are the edits required test.pl program on lines 189 and 194:

The only release note provided is below, which I found inadequate. If you find the trick before me, please post it.

Windows

• The fork() emulation has known limitations. See perlfork for a detailed summary. In particular, fork() emulation will not work correctly with extensions that are either not thread-safe, or maintain internal state that cannot be cloned in the pseudo-child process. This caveat currently applies to extensions such as Tk and Storable.
• It seems that some people are having problems with the ActivePerl MSI installer. The first thing to note is that you CANNOT install ActivePerl 5.16 over an older version of ActivePerl based on 5.14, 5.12, 5.10, 5.8, 5.6 or 5.005, such as build 1204 or any other 1200, 1000, 800, 600 or 500 series build. We have determined that some of these problems are due to other installations of Perl that may be pointed at by something in the environment.

  The sure-fire solution is to make absolutely certain that no other installations of Perl are on the target machine. Realizing that this is not always possible, you can follow these steps to ensure the other installations will not interfere.

  • Stop the “Windows Installer” service. This can be accomplished from the command prompt using the following command:

    C:\> net stop "Windows Installer"

    C:\> net stop "Windows Installer"

  • Temporarily remove or rename PERLLIB and PERL5LIB environment variables in the system environment.
  • Temporarily remove or rename the following registry values:

    [\\HKEY_LOCAL_MACHINE\Software\Perl] lib = <directory> (REG_SV) [\\HKEY_LOCAL_MACHINE\Software\Perl] sitelib = <directory> (REG_SV) [\\HKEY_LOCAL_MACHINE\Software\Perl] lib-<PerlVersion> = <directory> (REG_SV) [\\HKEY_LOCAL_MACHINE\Software\Perl] sitelib-<PerlVersion> = <directory>(REG_SV)

    [\\HKEY_LOCAL_MACHINE\Software\Perl] lib = <directory> (REG_SV) [\\HKEY_LOCAL_MACHINE\Software\Perl] sitelib = <directory> (REG_SV) [\\HKEY_LOCAL_MACHINE\Software\Perl] lib-<PerlVersion> = <directory> (REG_SV) [\\HKEY_LOCAL_MACHINE\Software\Perl] sitelib-<PerlVersion> = <directory>(REG_SV)

  • Proceed with the installation.

  Once the installation has completed successfully, the above actions may be undone although restoring the environment variables or the registry values may interfere with the proper operation of your new ActivePerl installation. In order to perform all of the above steps, you will need to have Administrative privileges on the target machine. If you do not have the required privileges you should contact your Administrator.

Somebody asked why you can’t implement MySQL triggers that write information when you want to stop the DML statement, like autonomous procedures in Oracle. The question was a surprise but I didn’t find anything on it, so here’s how you can do it. This is more or less like an autonomous process by leveraging both the InnoDB and MyISAM engine’s behaviors. This post leverages an earlier explanation of MySQL Triggers.

1. First you create a MyISAM table, which is a persistent store that auto commits when you’re other InnoDB tables can be transactionally dependent. Here’s a simple MyISAM logger table.

CREATE TABLE logger
( logger_id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, logger_event      VARCHAR(50)
, logger_table      VARCHAR(50)
, logger_instring   VARCHAR(100)
, logger_outstring  VARCHAR(100)
, created_by        INT UNSIGNED
, creation_date     DATE
, last_updated_by   INT UNSIGNED
, last_update_date  DATE) ENGINE=MyISAM;

2. Next, you create an on-insert trigger that changes an input but doesn’t stop the transaction. It also writes to the logger MyISAM table in the scope of the transaction.

CREATE TRIGGER contact_insert
BEFORE INSERT ON contact
FOR EACH ROW
BEGIN
 
  /* Check if last name contains a white space. */
  IF new.last_name REGEXP '^.* .*$' THEN
 
    /* Insert into an MyISAM table, which auto commits in the scope
       of a transaction. */
    INSERT INTO logger
    VALUES ( null
           ,'insert'
           ,'contact'
           , new.last_name
           , REPLACE(new.last_name,' ','-')
           , new.created_by
           , new.creation_date
           , new.last_updated_by
           , new.last_update_date );
 
    /* Replace the name for the INSERT INTO the CONTACT table. */
    SET new.last_name := REPLACE(new.last_name,' ','-');
  END IF;
END;
$$

3. Next, you create an on-update trigger that changes an update while aborting the transaction. It also writes to the logger MyISAM table because its outside the InnoDB scope of a transaction and auto committed on insert.

CREATE TRIGGER contact_update
BEFORE UPDATE ON contact
FOR EACH ROW
BEGIN
 
  /* Check if last name contains a white space. */
  IF new.last_name REGEXP '^.* .*$' THEN
 
    /* Insert into an MyISAM table, which auto commits in the scope
       of a transaction. */
    INSERT INTO logger
    VALUES ( null
           ,'update'
           ,'contact'
           , new.last_name
           , null           
           , old.created_by
           , old.creation_date
           , new.last_updated_by
           , new.last_update_date );
 
    /* Throw an exception to force the business user to see they
       can't update a last name with a white space. */
    SIGNAL SQLSTATE '42000';
  END IF;
END;
$$

4. Next, you create a test case with an INSERT and UPDATE statement that meets the condition of the triggers.

/* Insert a row meeting the trigger condition. */
INSERT INTO contact VALUES
( null, 1001, 1003,'Catherine', null,'Zeta Jones', 1001, UTC_DATE(), 1001, UTC_DATE());
 
/* Update a row meeting the trigger condition. */
UPDATE contact
SET    last_name = 'Zeta Jones'
,      last_updated_by = 1003
,      last_update_date = UTC_DATE()
WHERE  last_name = 'Zeta-Jones';

5. Last, query the logger table. You have a record inserted for both the allowed behavior and the aborted behavior. This means you have the ability to capture material that should never be inserted or updated into a table and who did it by leveraging the who-audit columns of the table.

SELECT * FROM logger;

+-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+
| logger_id | logger_event | logger_table | logger_instring | logger_outstring | created_by | creation_date | last_updated_by | last_update_date |
+-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+
|         1 | insert       | contact      | Zeta Jones      | Zeta-Jones       |       1001 | 2013-04-26    |            1001 | 2013-04-26       |
|         2 | update       | contact      | Zeta Jones      | NULL             |       1001 | 2013-04-26    |            1003 | 2013-04-26       |
+-----------+--------------+--------------+-----------------+------------------+------------+---------------+-----------------+------------------+
2 rows in set (0.00 sec)

This effectively delivers in MySQL the equivalent of an autonomous transaction in Oracle. The result from the non-critical trigger records the before and after value, while the results from the critical update trigger only record the before values because the event is aborted by raising an error in the trigger. As always, I hope this helps somebody looking for a solution.

My install instructions on the web site were old, somebody wanted me to publish another set of screen capture for the MySQL 5.6 install and configuration. This is it for Windows 7 using the downloadable MSI file.

Installation Steps

The installation from MySQL’s perspective is actually the installation and configuration of MySQL. For your convenience and reference, I’ve already installed the pre-requisites for MySQL. They’re:

• Visual Studio Tools for Office 20120 Runtime
• Microsoft .NET Framework 4 Client Profile
• Microsoft Excel 2007 or greater
• Microsoft .NET Framework 4 Client Profile
• Microsoft Visual C++ 2010 32-bit runtime
• Microsoft .NET Framework 4 Client Profile

Below are the installation steps after you download the current release .msi file. The icon should look like the one to the right. For this example, I”m using the mysql-installer-community-5.6.11.0.msi. Double-click the icon on your desktop or from your C:\Users\username\Downloads folder. While working through the steps, you can launch any of the small images to the left if you’d like to see what your screen should look like (generally with a right click to open in a new window).

1. The first screen is a Windows 7 dialog box. Click the Run button to install launch the MySQL 5.6 Installer.

2. The second screen is a Windows 7 dialog box. It advises you that the MySQL Installer is working and lets you cancel that operation. Don’t click the Cancel button unless you want to stop the MySQL 5.6 Installer.

3. The third screen is a MySQL Installer message box. It closes when ready to proceed. Ignore it, unless it’s there for more than a couple minutes. If that occurs you’ve got something wrong with your Windows installation or a very slow computer. If the former, kill the installation process; if the latter, wait patiently.

4. The fourth screen in the process is the first MySQL Installer screen. Here you choose what you want to do. You can install MySQL products, inquire about MySQL, or check physical resource components. Provided you installed the prerequisites listed above, you should be prepared to install MySQL Products. Click the Install MySQL Products link to proceed or one of the others to explore.

5. The second MySQL Installer screen is the license form. You must check the I accept the license terms checkbox to enable the Next button. Once the Next button is enabled, click it to proceed.

6. The third MySQL Installer screen connects to the Internet and finds the latest product update. You can check the Skip the check for updates (not recommended) checkbox to skip this but for the example we’ll check anyway. Click the Execute button to proceed.

7. The fourth MySQL Installer screen connects to the Internet and finds the latest product update. You can check the Skip the check for updates (not recommended) checkbox to skip this but for the example we’ll check anyway. Click the Execute button to proceed.

8. The fifth MySQL Installer screen acknowledges the latest update is what you’re installing. Click the Next button to proceed.

9. The sixth MySQL Installer screen gives you five choices for the installation, which are listed below. You most likely want to install the Developer Default, so click the Developer Default radio button and then, click the Next button to proceed.

   MySQL Server

   Both the client and server software for the MySQL Server

   MySQL Workbench

   The GUI application to develop for and manage the server.

   MySQL Visual Studio Plugin

   To work with the MySQL Server from VS.

   MySQL Connectors

   Connector/Net, Java, C/C++, OBDC and others.

   Examples and tutorials

   To help you get started with your development.

   Documentation

   Allows you to read the documentation offline.

10. The seventh MySQL Installer screen performs a system check for the pre-requisites, which I listed before the installation. Assuming you installed them, you should see a screen that confirms your system configuration is ready for installation. Click the Next button to proceed.

11. The eighth MySQL Installer screen performs displays the products that it’ll install, which are listed below and available in the full image to the left. Click the Execute button to install the products.

    • MySQL Server 5.6.11
    • MySQL Workbench CE 5.2.47
    • MySQL Notifier 1.0.3
    • MySQL for Excel 1.1.1
    • Connector/ODBC 5.2.4
    • Connector/C++ 1.1.2
    • Connector/C++ 1.1.2
    • Connector/J 5.1.24
    • Connector/NET 6.6.5
    • MySQL Documentation 5.6.11
    • Samples and Examples 5.6.11

12. The nineth MySQL Installer screen shows the installation by product and it can take a couple minutes. The screen to the left displays progress more than halfway complete. You don’t need to do anything in this step until all products are complete.

13. The tenth MySQL Installer screen shows the completed installation. Everything should install successfully, as shown in the image. Click the Next button to proceed.

This concludes the installation of the MySQL products. The next section shows you how to configure MySQL.

Configuration Steps

You have two basic options, the simple one and the advanced one. These steps will show you how to perform an advanced configuration. I’ve opted to maintain the step numbering from the beginning of the installation. Here are the steps:

14. The eleventh MySQL Installer screen is the first MySQL Configuration screen. You can click the Show Details button or begin the configuration. Click the Next button to proceed.

15. The second MySQL Configuration screen sets the server configuration type, enables TCP/IP networking (as opposed to a socket model), and lets you enable Advanced Configuration. For this installation, we enable the Show Advanced Options checkbox before you click the Next button.

16. The third MySQL Configuration screen sets the password and lets you create MySQL User Accounts. It’s much easier to let the install proceed and use MySQL Workbench to create databases, users, and roles; plus grant permissions through the GUI environment. Enter the root password twice, a trivial and unsecure password cangetin is what I recommend to my students who won’t have any meaningful information in the database. Make sure you can remember the password you enter. Clearly, a better password is required for real environments. After entering the password twice, click the Next button to proceed.

17. The fourth MySQL Configuration screen sets the Windows Service Name, and you should probably make sure there isn’t another MySQL56 service on the machine before you proceed. You have the choice of running the Windows Service using the Standard System Account or a Customer User account. Unless you’re an expert at Windows 7 administration, you should probably choose the Standard System Account as the one running the Windows 7 service. Click the Next button.

18. The fifth MySQL Configuration screen sets the logging options. You only need Show Query Log typically, but the Error Log is helpful. Make the choices and click the Next button.

19. The sixth MySQL Configuration screen explains the next step. It installs the sample files and example databases. You can see what you’ve installed when you click the Show Details button, which is what I did to get the image at the left. The default choice installs the samples and example databases, which can’t hurt. You’ll need the test database if you install DBD::mysql for Perl. If you don’t want them, you can drop them from the database.

20. The seventh MySQL Configuration screen explains you’ve completed configuring the MySQL Server. Click the Next button to proceed.

21. The eighth MySQL Configuration screen explains you’ve finished everything. You can copy the log file to clipboard, which allows you to see everything that was done. Click the Finish button to complete the installation and configuration.

Just one caveat (that’s a warning), this installation doesn’t put the MySQL executable into your System %PATH% variable. You’ll need to do that, and if I get a chance I’ll put a post together for that. I know one or two of my students may need it later.

I hope this helps those you are using the new installer for the first time. It’s a superior tool to the old one, which was also a good tool.

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.

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.