Those problematic characters, like the ampersand, are easy to manage with the %replace and %scan built-in functions.

When you're working with Web pages, certain HTML characters require special processing. Otherwise, your Web page may not display properly or your SQL statement may end prematurely. Microsoft ASP and PHP provide functions to support this requirement. So does RPG. In this article, I'll show you how to handle those special characters.

Suppose you're providing a Web page as a user interface, and it uses your DB2 database to store the data (on either the IBM i or some external server). And this user interface has text fields where the user can enter free-formatted text, such as an account name. You will have situations in which the account name may contain characters that have a specific purpose in HTML, such as the greater-than symbol (>). You could have your interface validate the field to reject it if entered. But what if you have a field that requires this character and you have to support it?

Another problem may occur when you have a name like "Tom's Computer Shack" because it contains a single quote, and single quotes may be used in an SQL statement to update records in the database. For example, you may execute the following SQL statement to add a new record:

INSERT INTO ACCOUNTS(NAME, CITY)

  VALUES('Tom's Computer Shack', 'Scranton')

See what happened there? The first value will look like it ended right after Tom because the single quote will close out that value parameter.

If you were to encode the HTML special characters, the encoded SQL statement would look like this:

INSERT INTO ACCOUNTS(NAME, CITY)

  VALUES('Tom's Computer Shack', 'Scranton')

And you would have no problems executing the statement.

Preventing SQL Injection

Besides supporting special HTML characters that are innocently being used to enter valid information, you can also encode HTML characters to prevent hackers from using SQL injection to damage your database. A hacker could implement SQL injection several ways, but we will look at the simplest case.

A hacker could exploit the ability to execute multiple SQL statements by inserting an additional SQL statement into the field that your program is using as input to a SQL statement. For our example, the SQL statement will be taking the NAME and CITY as input fields from the user interface. Suppose the user were to enter the following data into the field:

Scranton');DROP TABLE ACCOUNTS;SELECT * FROM ACCOUNTS WHERE NAME='

The resulting SQL statement would be this:

INSERT INTO ACCOUNTS(NAME, CITY)

  VALUES('Some Valid Name', 'Scranton');DROP TABLE ACCOUNTS;SELECT * FROM ACCOUNTS WHERE NAME='')

This would execute three SQL statements, with the following results:

• The first statement would complete the intended SQL statement.
• The second statement would delete the ACCOUNTS table.
• The third statement would satisfy the SQL syntax to provide an executable SQL statement. This is required to provide valid syntax to execute because it knows that it is expecting a closing quote at the end of the SQL code.

If you were to encode the HTML special characters, the encoded SQL statement would look like this:

INSERT INTO ACCOUNTS(NAME, CITY)

  VALUES('Some Valid Name', 'Scranton');DROP TABLE ACCOUNTS;SELECT * FROM ACCOUNTS WHERE NAME='')

Because the single quote was encoded into ', the CITY parameter was treated as one big string. This statement would successfully execute and cause no damage. You would most likely store the user name with the recorded transaction so you may want to have a conversation with the identified user.

HTML Encoding and Decoding in PHP and ASP

This is not a problem for languages that expect this kind of thing. You'd just use special functions to encode the characters before you store them in the database.

In PHP, you would use htmlspecialchars:

<?php

htmlspecialchars("Tom's Computer Shack");

?>

In Microsoft ASP, you would use HTMLencode:

<%

Server.HTMLencode("Tom's Computer Shack");

%>

These functions would handle most HTML characters, and decode functions are available as well.

When using the single quote in ASP, you would need to expand it a little further with a custom function to also convert a single quote (') to &#039; or double it up to be double quotes (''). And in PHP, you can specify the behavior of quotes with flags. But let's get back to RPG.

After encoding the characters, you would end up with this: "Tom&#039;s Computer Shack." You're probably thinking, "Why do I need to worry about this in RPG?" The problem arises when you're dealing with encoded data in RPG that is also used for reporting or billing. The account name is now stored with an HTML-encoded value, and you wouldn't want to print that on the customer's statement. So you need to decode it before you print it.

You may also need to support an alternative means of creating the data with a green-screen, so you would also want to encode the data before you store it to make it friendly for the Web page and SQL.

Replacing a String Within a String

Before we get into the specialized HTML encoder and decoder, let's put together a little procedure (which we can reuse for numerous things) that will replace a string within a string using the %replace and %scan built-in functions (BIFs).

The %replace BIF needs to know which string to replace, where to replace it, and how much of the old string needs to be replaced. You can retrieve most of this information using the %scan BIF. So put them both together, and you could write the follow flexible procedure to do all of this for you.

     D replaceString...

     D                 PR         65535A   varying

     D  argSource                 65535A   const varying

     D  argOld                     1024A   const varying

     D  argNew                     1024A   const varying

       **********************************************************************

      *    PROCEDURE NAME: replaceString

      *             INPUT: Source String, Old String, New String

      *            OUTPUT: String with all occurrences of old string replaced.

      **********************************************************************

     P replaceString...

     P                 B                   EXPORT

     D replaceString...

     D                 PI         65535A   varying

      * PASSED PARAMETER LIST *

     D argSource                  65535A   const varying

     D argOld                      1024A   const varying

     D argNew                      1024A   const varying

      * LOCAL VARIABLES *

     D svOut           S          65535A   varying

     D svPosi          S             10I 0

     D svOffset        S             10I 0

      /free

       svOut = argSource;

       svPosi = %scan(%trim(argOld):svOut);

       dow svPosi > 0;

         svOut = %replace(%trim(argNew):svOut

                         :svPosi:%len(argOld));

         svOffset = svPosi + %len(argNew);

         svPosi = %scan(%trim(argOld):svOut:svOffset);

       enddo;

       return svOut;

      /end-free   

Now that we have this flexible new procedure, let's use it to assist with the encoding and decoding of HTML characters. The following table shows the characters that we will be converting to avoid any issues. There are also more HTML special characters that you can also encode if you desire, but this set of codes supports the functionality that I commonly require. I also went a little bit further and converted the carriage return to <br> to make it look better when being sent to an HTML page.

The following program will encode and decode the <b>''Tom && Jerry''</b> string of bytes. I deliberately used double ampersands to demonstrate the multiple replacements in the procedure.

     D htmlEncode...

     D                 PR         65535A   varying

     D  argIn                     65535A   const varying

     D htmlDecode...

     D                 PR         65535A   varying

     D  argIn                     65535A   const varying

     D*

     D inBytes         S            100A

     D outBytes        S            100A

     D displayBytes    S             52A

     D posi            S             10I 0

     C*

      /free

       inBytes = '<b>''Tom && Jerry''</b>';

       displayBytes = 'Before: ' + %trim(inBytes);

       DSPLY displayBytes;

       outBytes = htmlEncode(inBytes);

       displayBytes = 'Encoded: ' + %trim(outBytes);

       DSPLY displayBytes;

       outBytes = htmlDecode(inBytes);

       displayBytes = 'Decoded: ' + %trim(outBytes);

       DSPLY displayBytes;

       *inlr = *ON;

      /end-free

     P                 E

      **********************************************************************

      *    PROCEDURE NAME: htmlEncode

      *             INPUT: Source String

      *            OUTPUT: String (HTML Encoded)

      **********************************************************************

     P htmlEncode  B                   EXPORT

      *

     D htmlEncode  PI         65535A   varying

      * PASSED PARAMETER LIST *

     D argIn                      65535A   const varying

      * LOCAL VARIABLES *

     D svOut           S          65535A   varying

      /free

       svOut = argIn;

       svOut = replaceString(svOut: '&': '

       svOut = replaceString(svOut: '"': '&

       svOut =