Comments/Ratings for a Single Item

While scouring the site for PHP scripts with mysql code, I came across a function called loggedinas(), which would return the userid or openid of the person signed in. Signing in with openid is no longer available due to changes made on the website that handles this. So a function that returns the openid of someone signed in is no longer useful. But in case it would be useful to have a function that returns the userid of the person signed in, I modified login() to return the userid of the person signed in if both arguments passed to it are empty strings, which by default they will be. So, login() called without arguments will return the userid of the person signed in if anyone is signed in. It will otherwise return false. For the sake of consistency, it will now use the userid as its true value, and it uses the boolean false for false.

The login() function previously outputted 1 for logging in with a userid and 2 for logging in with an openid. I had to change some code that relied on this to enter the previous comment. It doesn't look like any other code relies on this convention, though let me know if you find any that does.

I have just expanded the capabilities of table_row() and table_rows() from index/fpd-indexing-funcs.php. These two functions generate a prepared SQL SELECT statement and execute it with PDO. The former returns the first row found, and the latter returns an array of all rows found. On failure, each returns false. All changes to them are backwards compatible with previous uses of them, so that no code is broken by the changes. The main change is that I adapted them both to accept a variable number of arguments. Previously, they could accept a single column/value pair in the second and third argument places. They can now accept any number of column/value pairs or even none at all. Each column/value pair will take up two arguments, listed one after the other. The column name should come first, followed by the value name. Every column/value pair will be added to the WHERE clause. They should be placed after the first argument, which is the table name. If there is a last unpaired argument, its value will be appended to the generated SQL as is. This is the same as before, except that it is not always the fourth argument anymore. To avoid the risk of SQL injection, only the values of columns should be used with data entered by users. The table name, the column names, and the last unpaired argument should not be copied from user-entered data.

I wrote a valid_table_name() function that will indicate whether the table name passed to it is the name of a table in the database. This is now used by functions that take a table name as an argument to prevent attempts to access invalid table names.

I just wrote another function called valid_column_name(). It takes a table name and a column name as its two arguments, and it returns whether the column name is the name of a column in that table. To avoid redundancy, it does not check whether the table name is valid. This should have already been done with valid_table_name. Using these two functions inside of table_row(), table_rows(), and get_field(), these functions for reading the database should now be immune to SQL injection from user input.

No, wait. Since the unpaired tail argument for table_row() and table_rows() is appended as is, this can be used for SQL injection. Therefore, this value should never be determined by user input.

I found a call to table_rows() which looked like this:

table_rows("Person", "1", "1", "ORDER BY LastName, FirstName, PersonID");

The purpose of the "1","1", was to include 1=1 in the SQL, which would return the entire Person table. This was a kludge for using table_rows() with a vacuous WHERE condition that would return everything. It had to be done this way, because the second and third arguments used to be reserved for the column and value that specified the WHERE condition for the search. Since I changed the function to use a variable number of arguments, this is no longer needed, and this will return the same result:

table_rows("Person", "ORDER BY LastName, FirstName, PersonID");

Furthermore, the error correction I added today with the valid_column_name() function prevents the original kludge from working. So, wherever a kludge like this has been used to return a full table, it can and should now be deleted.

Since I thought I had fixed up get_field() to use multiple arguments, I found out I hadn't when I tried to use it that way. So I copied some code and fixed it to work that way too. It will now take the same kind of multiple arguments as either table_row() or table_rows().

I wrote a new function today called safe_email(). This takes the arguments $to, $subject, $message, $from, and $replyto. The last one is optional, the rest are required. The first three are the same as the PHP function mail(). Instead of including an argument for additional headers, these are constructed inside the function. Since we haven't been using the parameters argument of mail(), it was left out. Unlike mail(), safe_email() does validation on email addresses, making sure they are singular, properly formed, and listed in the database. It also checks whether a $to address has been marked as dead or belongs to a domain we know we cannot currently send email to, and it checks against user preferences for receiving certain types of email. If everything checks out, it will send the email. It can also recognize UserIDs and PersonIDs and retrieve the appropriate email address from the database.

I replaced mail() with safe_email() in login/registeruser.php, login/change_email.php, login/change_password.php, index/addcomment.php, and play/pbm/sendmove.php, and I tested it in most of them.

The safe_email() function normally checks whether a To address is listed in the database, and I initially just wrote in an exception for changing an email address. But since Game Courier invitations may also be sent to an address that is not in the database, I replaced this specific exception with a flag that allows an exception for a particular use. I added an optional sixth argument called $onlytomember, and it defaults to true if you don't include it. When the function should be allowed to send email to someone who is not a member, the sixth argument should be set to false. When you need to set the sixth argument, but you don't need to set the fifth argument, set the fifth argument to "", which is the same as its default value.

I also just realized that an exception was required for joining, and that has now been added.

I modified the connect_to_database() function to no longer use mysql_connect(). While I was converting from mysql functions to PDO methods, I had it open both a mysql connection and a PDO connection. Now it opens only a PDO connection. I also made some further modifications. I set it to use the charset utf8mb4. One consequence I saw of this was that the umlauted i in Caïssa Britannia got messed up. I fixed this by reentering the name. If it happened to this, it may have happened to other game listings in the database. I also set PDO::ATTR_EMULATE_PREPARES to false, which means it will now use native prepares, which pass the SQL and the values separately. I learned that I had been using emulated prepares, which actually puts everything together on the PHP side before passing it to the database. I read that this is supposed to be 100% secure when the charset is set to utf8mb4, but I still wanted to try to get it to use native prepares. Some scripts were giving me problems for a while, and I went back and forth between native and emulated prepares while trying to work out these problems. These problems now seem to be fixed, and the scripts seem to be working well with native prepares. But maybe there are scripts I still need to test. So let me know if anything seems amiss.

The table_row(), table_rows(), and get_field() functions can now recognize the "%" wildcard used by LIKE, and if found, they will use LIKE instead of =. This is now being used on the 404 page to help find the page the person was trying to get to.