DatabaseDesign

This page contains notes about database design. For the moment the page is a stub.

Web programming with PHP

This section contains notes I took while I was coding Little Go for the Web.

General Database access principles:

• Only use prepared statements to prevent SQL injection attacks.
• Whenever reading data from the database that may have been supplied by a user, that data is immediately put through htmlspecialchars() to prevent XSS attacks.

The following notes target MariaDB:

• All ID columns are numeric and have type bigint to prevent overflows.
• All ID columns use AUTO_INCREMENT so that the code does not have to deal with generating new IDs.
• The session key is defined using 128 characters length to guarantee sufficient space to store the session ID generated by the PHP function session_id(). The length of such a session ID is variable and depends on various configuration elements in php.ini, among them the hash algorithm. I didn't decide on length 128 because of any official documentation, but because of a user comment in the PHP docs for session_id(). Originally I had planned to use a UUID as the session key, in that case it would have been sufficient to use length 36 for the session key. Unfortunately PHP does not have a built-in function to generate UUIDs, and since I didn't want to use a separate PHP library just for generating UUIDs, I decided to fall back on session_id().
• For time data I am using a numeric column to store the number of seconds since 1 January 1970. The data type should be bigint to get around the Y2038 problem. In the PHP code, however, I'm using the data type int because that is the data type returned by the time() function. The size of PHP's int is platform dependent.