The SQL Action and Editing the Database

When a hack adds new features, they are often tracked in some manner by the SQL database of the forum. In order to get this working, you will need to make changes to the database. Modification authors will include these changes using one or more of three methods. The first method, and most popular in more recently created modifications, is to include an installer script file. Installer files are special files with a .php filename extension that can make database changes for you. They are typically named install.php or db_update.php, but the name can have some variety. To use these files, you normally only need to place them in your forum's directory structure (the Copy action handles that) and run the file.

This part might be confusing to you. People learning how to install modifications often ask, "What does it mean to run the file?" To run a .php file, you need to visit it in your web browser like a normal web page. A good comparison comes from thinking of your forum's viewtopic.php, which displays all topics. Every time you read a topic in the forum, you are running the viewtopic.php file. Just visiting your uploaded db_update.php file will begin the process of editing the database. Some complicated installers may walk you through several steps.

Many installer scripts will not work unless you log in to the forum using an administrator account before running the file. Always be sure to log in first, and delete the installer file immediately after running. Leaving an installer script in place can be a major security risk!

A second method of including database changes is to place a list of SQL query commands in the installation instructions under an SQL action. The third method places the same kind of list in a separate text file, which might be named sql.txt or schema.sql. Other names are possible, but the important thing to note is that any file with an extension of .sql is a text file containing SQL queries. When either of these two methods is used, you will need to do one of two things: create your own installer script or run the changes manually.

It is not very hard to create your own installer script. In fact, there are generators that can create a db_update.php file for you! One such generator can be found at http://www.phpbbhacks.com/forums/db_generator.php. Simply copy the SQL queries from the SQL action or file and paste them into this or another generator, submit the form, and you will be presented with the code of a db_update.php file. You can download the file directly from that page, place it in your forum, and run the file in the same way you would run an author-supplied installer.

Some SQL queries may not be compatible with db_update.php generators. These will usually produce installer scripts that display parse errors when run. If this happens, visit a friendly phpBB support forum and ask for help with making the queries compatible.

In order to make database changes manually, you can use phpMyAdmin or another similar database management utility. However, you may need to edit the SQL queries before you can do this. When you installed phpBB, you had an option to enter a database table prefix. phpBB uses the value of this option for the beginning of all database table names. Each query in a modification should mention a database table name such as phpbb_posts or phpbb_config. If your chosen table prefix were myforum_, you would need to change the table names to myforum_posts and myforum_config before using the queries. Installer script files will typically make this change automatically, so you should consider generating one if you are uncomfortable with this process. These are some queries as they might appear before and after changing the table prefix:

CREATE TABLE myforum_tracker ( tracker_id mediumint(8) DEFAULT '0' NOT NULL, tracker1 tinyint(1) DEFAULT '0' NOT NULL,tracker2 varchar(40) NOT NULL,PRIMARY KEY (tracker_id),

); INSERT INTO phpbb_tracker (tracker_id, tracker1, tracker2) VALUES (1, 3,'Some text'); INSERT INTO myforum_tracker (tracker_id, tracker1, tracker2) VALUES (1, 3,'Some text');

With the queries in the proper format, open phpMyAdmin and, if necessary, select your database in the left column. Click on the SQL tab or Query Window link. Either of these will load a page containing a large text box. Copy the SQL queries from the modification instructions or text file and paste them into this box. A click of the Go button should run the queries on the database automatically.

The Open Action

This simple action tells you the name and location of a file you need to edit. You should open this file in WordPad, or your favorite text-editing program, and be prepared to make changes to it. If you have not already backed up the file, do so now. All the other actions that follow an Open action should be preformed on the file listed in this action until you reach the end of the instructions or another Open action.

# #-----[ OPEN ]------------------------------------------ # includes/functions.php

The Find Action

Each Find action is followed by one or more lines of code that should be contained within the file in the last Open action. Your goal is to locate this code, which is supposed to be an easy task. Most text-editing programs, including WordPad, have a Find or Search function that makes searching the entire file quick and easy. The Find function should be accessible through the menus of the program or, in Windows programs, by pressing Ctrl+F.

Unfortunately, there are some problems that can make finding lines a chore. First, several versions of phpBB have been released. Each version has certain code changes, so phpBB

2.0 will have some modifications for early releases where the Find code is very different than the code in the latest version. In these cases, it is often best if you do not install the modification unless the relevant changes between versions seem minor. Another problem is that some modifications try to find code that has been changed, or even removed, by other modifications already. Some authors have tried to bypass this problem by only listing the first few characters of each line to find. That practice has created yet another problem due to misinterpretation of the actions that follow Find instructions. Finally, some lines of code appear more than once in some files. You may need to edit only a specific appearance or all instances.

How can we solve these problems? Sometimes it is not very easy. If you cannot locate some Find code, the first solution to try is searching for smaller portions of the code. Suppose you have a hard time locating the line of this instruction:

Looking for a key part of the line, like SOME_TPL_VARIABLE, may allow you to find the line even if it has already been changed by another modification or version of phpBB. The ability to adapt lines of code is a key skill when installing modifications and you may need to develop this skill over time.

When dealing with lines or blocks of code that appear in a file several times, keep in mind that most modifications are written based on a top-to-bottom order. Say you have instructions containing two Find commands for one file, with the code of the second appearing twice in the file. The author usually means for you to locate the first appearance following the code of the first Find command.

The Addition Actions

Three actions can add a new code to a file: Before, Add; After, Add; and Replace With. Each of these addition actions will always follow a Find action, and operate in reference to the code in the Find action. In the case of Before, Add actions, the code following the action should be placed into the file on a line before the code of the Find action. For After, Add actions, the codes go onto a line after the Find action's code. A Replace With action will delete the Find action's code and substitute the new code for the old.

For the most part, these are simple steps. There is one point about both Before, Add and After, Add actions that cannot be stressed enough, and that is always place the new code on a new line, separate from all other code. Some modification authors, as has been mentioned, include only partial lines of code in their Find actions. The problem with this practice is that modification users tend to add new code right after that in a Find action, on the same line. This places new code right in the middle of the original code, creating a series of syntax errors that can make a forum inaccessible. If you always add code before or after Find code by inserting it on a completely new line, you should be able to avoid this problem.

To illustrate how the code of a file should look, here are some sample action sequences and the correct resulting code. For the sake of these examples, assume a file containing the following code has already been referenced in an Open action.

<?php

$number1 = $number2 = $number3 = 0; $number1 = $x + $y; $number2 = $y + $z;

$total = $number1 + $number2 + $number3; echo 'The total is: ' . $total;

?>

First, a simple Find and After, Add combination:

# #-----[ FIND ]------------------------------------------ # $number1 = $x + $y; $number2 = $y + $z; # #-----[ AFTER, ADD ]------------------------------------------ # $number3 = $a + $b;

This sequence should create:

$number1 = $number2 = $number3 = 0; $number1 = $x + $y; $number2 = $y + $z; $number3 = $a + $b;

$total = $number1 + $number2 + $number3;

A Find and Before, Add combination is next:

# #-----[ FIND ]------------------------------------------ # $number1 = $x + $y; $number2 = $y + $z; # #-----[ BEFORE, ADD ]------------------------------------------ # $my_array = array('a' => 1, 'b'=> 2, 'x' => 3, 'y' => 4, 'z' => 5); while( list($key, $value) = each($my_array) ) {

$$key = $value;
}

After applying this sequence, the file would contain:

$number1 = $number2 = $number3 = 0; $my_array = array('a' => 1, 'b'=> 2, 'x' => 3, 'y' => 4, 'z' => 5); while( list($key, $value) = each($my_array) ) {

$$key = $value;
}

$number1 = $x + $y; $number2 = $y + $z;

A Find action followed by a Replace With action could be:

# #-----[ FIND ]------------------------------------------ # $total = $number1 + $number2 + $number3; # #-----[ REPLACE WITH ]------------------------------------------ # $average = ($number1 + $number2 + $number3) / 3;

That sequence would transform the end of the file into this snippet of code.

$average = ($number1 + $number2 + $number3) / 3;echo 'The total is: ' . $total;

?>

Finally, here is another version of the earlier Find and After, Add sequence. This time, the Find action contains only portions of the lines. The result of applying the sequence should be the same as if the full lines were given.

# #-----[ FIND ]------------------------------------------ # $number1 = $number2 = # #-----[ AFTER, ADD ]------------------------------------------ # $number3 = $a + $b;

The In-Line Actions

Despite what you have just learned about the importance of adding code on new lines, sometimes you will need to change a piece of code within a line. The In-Line actions are used for this. There are four of these: In-Line Find; In-Line Before, Add; In-Line After, Add; and In-Line Replace With. Ideally, you will see a Find action with a long line of code, followed by an In-Line Find with a short snippet of that line, then one of the other three In-Line actions. Use them just as you would their normal counterparts, but this time do not add the new code on a separate line. You will actually insert the code inside the current line. In several modifications that use In-Line actions, you may have to change a line in several different places.

Using the same short file above, here is an example of an In-Line Replace With action sequence that edits two parts of one line.

# #-----[ FIND ]------------------------------------------ # echo 'The total is: ' . $total; #

#-----[ IN-LINE FIND ]------------------------------------------

#

total

#

#-----[ IN-LINE REPLACE WITH ]--------------------------------------

#

average

#

#-----[ IN-LINE FIND ]------------------------------------------

#

$total

#

#-----[ IN-LINE REPLACE WITH ]--------------------------------------

#
$average

That sequence alters the end of the file to look like:

$average = ($number1 + $number2 + $number3) / 3;echo 'The average is: ' . $average;

?>

The Last Action

Almost every modification's instructions end with a variation of this action:

# #-----[ SAVE/CLOSE ALL FILES ]--------------------------------------

#
# End

This is the last step of the instructions. It serves as a reminder to save your changes, close any remaining open files, and transfer edited files to your forum if necessary. Once you for accounts with different permissions. For testing purposes, it is best if you only install one modification at a time. If you were to install three or four modifications simultaneously and you encounter an error in your forum, you might have a hard time tracking down the cause.

The following table provides a quick reference of the modification Action:

Action Name	Definition
Copy	Copies one or more files from one location to another.
SQL	Runs one or more SQL queries on your forum's database. These should usually be completed before installing the remainder of the modification.

Action Name	Definition
Open	Opens the file in preparation for editing. Every action following this until the last action, or another Open action, should be performed on this file. It is followed by a single filename.
Find	This locates the code in the file and is followed by code.
After, Add	Places the following code on a new line after the code in the preceding Find action. It always follows a Find action.
Before, Add	Places the following code on a new line before the code in the preceding Find action. It always follows a Find action.
Replace With	Deletes the code found in the preceding Find action and puts the following code in its place. It always follows a Find action.
In-Line Find	Usually follows a Find action. Locates the following code inside a longer line of code.
In-Line After, Add	Places the following code after the code in the preceding In-Line Find, on the same line of code. It always follows an In-Line Find action.
In-Line Before, Add	Places the following code before the code in the preceding In-Line Find, on the same line of code. It always follows an In-Line Find action.
In-Line Replace With	Deletes the code in the preceding In-Line Find and put this code in its place on the same line of code. It always follows an In-Line Find action.
Save/Close All Files	Signals the end of file changes and reminds you to save your changes. It is always the last action of a modification.
Add SQL	This is an older form of the SQL action, with the same meaning as that action.