Basic Attributes

Design Notes

These are notes you can input that help you and other production staff understand your confirmation elements logic and purpose. This text is never used in the live form in any way, it is only for internal development.

Basic Attributes

When using the Query module, you will most likely be talking to a database other than the one RackForms is installed on. Thus, the fields below are vitally important for making a connection to that external database.

Making this connection is the same as any other PHP->SQL interaction, in that you provide a DB Host, user name, password, etc. The only wrinkle is that RackForms offers a tiered connection model, with each tier providing a slightly different path to the final connection. This tier system ultimately speeds up and secures the development and deployment process, but you should take a few moments to understand how it works.

The RackForms Tiered Connection Model
The first tier is the default config.php file which is based in:
app/movefiles/config.php.

That means If you provide no values for the DB Connector file and DB Vendor, DB Host Name, DB Schema Name, DB User Name, and DB Password fields, then the config.php file in your job folder is used. However, this may not be what we need with an SQL+ job, as we may not always need to write to the RackForms database, which is what the config.php file connects to.

This is so because the connection values included in the 'default' config.php file originate from when you first installed RackForms on your server, that is, the connection information would be the same as your RackForms application.

This makes sense for the Simple SQL module, as the RackForms entry viewer runs on the same database as your RackForms application. However, with the SQL+ module you will have a much greater chance of needing to talk to a different database.

The second tier are the DB Vendor, DB Host Name, DB Schema Name, DB User Name, and DB Password boxes. If you place values in these fields the config.php is ignored and the values you set in those boxes used instead.

The third tier is the DB Connector File box. Placing a value in this box means we override the default config.php file, as well as any DB Vendor, DB Host Name, DB Schema Name, DB User Name, and DB Password settings. This is considered the 'safest' model, as we could effectively hide connection information from clients or less privileged production workers by restricting access to the connection file pointed to in this field.

To set up and use this method we must:

a) locate the "original" config.php file in app/movefiles/config.php
b) copy this file to the new location defined by the DB Connector File box
c) update the values in the config.php file to match the database being connected to.

Now when we run the job RackForms will consult the custom config.php file for database connection details. This can be especially handy as it means the user of the job in the RackForms editor doesn't need to know the database details, just the path to the config file.

To summarize: if you leave everything blank, RackForms will include and use the 'default' config.php file. If you place values in the DB Vendor, DB Host Name, DB Schema Name, DB User Name, and DB Password boxes, they will be used over the config.php file. Finally, if you include a value in the DB Connector File, the connection variables in that file will override all other values.

DB vendor

Choose the database vendor you will be connecting to for the dynamic query.

DB Host Name

The host of your SQL provider - could be 'localhost', an ip address, or a server/instance name.

DB Schema Name

The Database catalog you wish to query.

DB User Name

Database user name

DB Password

The password for this database connection.

DB Connector File

If you input a relative path in this box, RackForms will use that file for all database connection information for this query.This means you can 'secure' your forms by making sure access to important database information is limited to only those with access to this file.

To create your own DB Connector File, simply copy the contents of your existing config.php file from your /app/movefiles/ directory and paste it into a new file named what ever you like.

You will then need to change the values of the fields shown below to match the values of the datasource you wish to use:

$db_type = 'mysql';
$db_host = '127.0.0.1';
$mysql_socket = '';
$mysql_port = '';
$db_user = 'RackForms';
$db_pass = 'test';
$db_catalog = 'RackForms';

Now in the DB Connector File field place a path to the file you just created relative to your forms final destination. For example, by default all RackForms output is placed in:

RackForms/output/forms/

Which means if I placed my DB Connector file in:

RackForms/output/forms/config.php

My DB Connector File field would have:

../config.php

as this file would indeed be one directory up from my form, which if was called external_db, would be located at:

RackForms/output/forms/external_db

Now when we run the form RackForms will not use the config.php file located in the job folder (indeed, one will not even be created unless I have a File Upload module in my form that doesn't also have a DB Connector File specified), which means in principal, any users with access to the RackForms job folder will never see any database login info, and so long as they did not have access to the folder with the External DB Connector File, not see any DB information period.

MySQL SSL Options

RackForms supports secure MySQL connections using SSL. To enable this functionality your MySQL Server will need to support SSL connections, which usually means modifying your servers my.cnf file to supply locations to your ca, server-cert, and server-key file. You may also, depending on your MySQL version, need to enable SSL connectivity. Learn more about this process and settings here.

As a very brief demo, the my.cnf settings we use internally are as follows:

ssl-ca=/mysql-ssl/ca.pem
ssl-cert=/mysql-ssl/server-cert.pem
ssl-key=/mysql-ssl/server-key.pem
ssl

Once the server has been configured to allow SSL connections, RackForms will need to know the location of three key files. These items are described below. To keep things simple, the names used in this document assume we've created our keys using the process described here. Please note the .PEM file locations can be relative or absolute.

MySQL Client Key File

The client key file will be called client-key.pem. This is our private key used in the SSL handshake process.

MySQL Client Certificate

The client certificate will be called client-cert.pem, and is used to publicly identify us using the CA file on record.

CA Cert

The last file will be called ca.pem, and is a "shared" file between the server and client, as in we use the same file.

As an example then, if I created a folder called mysql-ssl and placed my .PEM files within, the values I'd use in RackForms would be:

MySQL Client Key File - /mysql-ssl/client-key.pem
MySQL Client Certificate - /mysql-ssl/client-cert.pem
CA Cert - /mysql-ssl/ca.pem

Important Notes On MySQL and SSL Support

Using localhost: First, when using a localhost setup we'll often use localhost as our Database Host Name. The problem with this is on a local machine localhost uses sockets instead of TCP/IP, which means we're trying to encrypt a local communication channel. This simply doesn't work and we're receive a "doesn't support SSL/crypto" message. The solution here is to use 127.0.0.1 as our host name, which forces TCP/IP on a local connection.

Using Self-Signed Certificates: One common approach we may take when using SSL and MySQL for the first time is to set up a test instance on a localhost machine using self-signed certificates. This is great in concept but unfortunately creates some unique challenges. When we try and connect we may be greeted with the error:

SSL3_GET_SERVER_CERTIFICATE:certificate verify failed

The problem here is new security features in PHP 5.6 and above, along with the MySQL native driver, creates verification issues when using self-signed certs. If this is the case we can disable certificate validation using the Verify MySQL SSL Server Cert option.

Verify MySQL SSL Server Cert

As noted above, when using self-signed certificates it may be necessary to disable certificate peer validation. This option should always be set to Yes in production environments, but if you have problems connecting on a local network or machine, try the No setting.

It's very important to note setting this to No completely defeats the purpose of using SSL, and should only be done on a non-production, local machine.

If you've made it this far we should only have one final consideration, the encryption cipher:

MySQL SSL Cipher

When connecting to a MySQL server using SSL, the client and server need to negotiate an encryption protocol and cipher. In the best case scenario this happens automatically, and the end result is the server automatically selects the most secure grouping possible.

Unfortunately this doesn't always work (especially when using self-signed certificates or builds of MySQL compiled against older versions of OpenSSL). The most common case is when your MySQL server only supports TLS V1, in which case the default cipher (DHE-RSA-AES128-GCM-SHA256), is not supported. If this happens you'll need to choose a different cipher from this list. The best way to do this is to start with the lowest value in the list, then work your way up until it fails again. Then, back down to the last working option.

The general rule will be if the default option fails, none of the 256-bit variants will work. This is usually considered a security issue, and you should take steps to update your MySQL server and/or PHP version to ones that support the latest security protocols.

 

Confirmation /Query Condition

Confirmation Condition PHP Code

This field allows you to specify a condition which must be met for the query field to be processed. Plese note that as of Build 666 we can now use the full set of tokens, as described in the popup help window in the editor.

The logic takes the form of:

Variable 1 | Condition | Variable 2

For example, let's assume we have a radio item in our form with the Name/Value of opt_1. The radio item has two possible vales, 'Yes' and 'No'. If the value of opt_1 is 'Yes' we want to send an email, if no, we do not.

We would write the Confirmation/Query Conditional PHP Code as such:

#{opt_1} == 'Yes'

In other words, we use the token for our field variable like we would in other property boxes, that is, a pound sign (#) followed by the Name/Value of the field in braces. Recall that at run time this token evaluates to the value set by our forms users, which in this case will be the value of the radio button with the Name/Value of opt_1.

We then set the comparison, in this case out comparison is equal too, denoted with the double == sign. Finally, because we know our radio item is a string value that can be 'Yes' or 'No', we wrap the value we want to check for in single tick marks.

On the raw code side, RackForms wraps the token call in an isset() block, which is further wrapped in the proper PHP if() syntax:

if(isset($_SESSION['qs']["{$_SESSION['entry_key']}"]['opt_1']) && $_SESSION['qs']["{$_SESSION['entry_key']}"]['opt_1'] == 'Yes') { // condition code start

When run, if the users set value for the radio box was 'Yes', we would process this confirmation element.

Query

SQL Debug/Data Return Mode

This select item lets you choose the type of debug mode you want. The first option is No Debug, which as the name implies, will not return any value if an error occurs, and is the mode any production form should use.

The second mode is Error Messages, which will return any error messages the query produces. Finally, the last option, Data Dump, will echo all rows the query produces, or if it is a lastInsertId() query, the lastInsertId();

SQL Code

In this field we create the SQL query to run. Like all other SQL code fields, RackForms uses Prepared Statements to handle parameters, which means for every external parameter we assign a question mark 'placeholder'. These placeholders are then defined in the Variables field directly below. It should be noted however, that if you have a simple, static variable such as a number or text block, you can input this value directly inline with the SQL code like any other query.

Variables
This text area holds a comma delimited list of any variables used in the SQL Code as defined by the question mark placeholders (?) above in the SQL Code text box. This list must sequentially match the SQL Code list--the first question mark placeholders value will be defined by the first SQL Variable item.

RackForms allows you to use many types of variables, from direct values placed inline with the SQL query to predefined values such as TIMESTAMP, to token based parameters of which each has a special syntax to retrieve at query runtime.

Result Set Variable Name

This important field is how you define the name of the PHP variable any result value will be assigned to. This field can take on two main values:

1. The first is an INT value if you have the Return lastInsertId() checkbox checked (please see below).

2. If the lastInsertId() checkbox is left unchecked you get an array of any values returned by the query. It is important to note this array is created using the PDO::FETCH_BOTH constant, which means every field item in the array has a numerical and INT based index.

This is important because when creating a Query for an element you may not know or be able to specify the field name as you would in an associative array, in those cases you can simply use the numerical index.

Most importantly, it is important to note that PDO creates an array of array's in that each result row is a key in the 'main' result array, with each 'row' being itself an array. This means if you want to retrieve a single value from the first row of a result set you need to specify the first index as in:

$result_var[0]["db_field"];

That is, the name of the PHP variable which holds the result, the index of the row as defined in the brackets [0], and the database field name.

Finally, you will notice that when you set this name or change it the field item in your Form edit area will update accordingly.

Function Call Definition

This very handy feature allows us to wrap the Query Module Database call into a standard PHP function, which means we can 'call' the query from our page code, Builder Function calls, and so on.

So as an example, in a PHP Top Code block we may need to receive a database query result if some condition is met, but only if that condition is met.

To handle this we would add the following code to this field, which is simply a PHP function signature:

get_names($job_id)

Which would say, wrap the Query code in a PHP function call of get_names, which accepts a parameter called $job_id.

Please note that we DO NOT use <?php tags, nor do we add braces { }. We simply put the function name and any parameters in parenthesis.

How To Handle The Result Of The Query

With our function signature defined, we can now call the query as needed, and then, based on the Function Call Returns Result Variable setting, can create a looping echo statement in the Code To Run After Query block, or return the entire result to the functions caller.

Function Call Returns Result Variable

This option is directly tied to Function Call Definition, in that unless we have a function call definition set, this field is ignored.

When set, this means we use the PHP return keyword to return the entire database result set from the function. This is desirable in situations where we need to further process the data in another code block.

As an alternative, we can leave this setting unchecked and simply add code to the Code To Run After Query block to directly iterate and echo out values. This is very handle for Builder forms, in that we can call the database with a query and then directly output the values into our Builder template.

Return lastInsertId()  

Checking this box means any auto-increment value from an INSERT query to a table defined with one will be returned to the variable name you define. This is very handy for creating say, a user account for a login system, in that the first query would return the key of the new user record in an 'admin_login' table (the lastInsertId), to which you can then take that index and assign it as a value to a second SQL query for a related 'personal_information' table.

Please keep in mind if you have this box checked you will no longer be able to retrieve any result set values, as would be the case in normal SQL anyway.

To retrieve this value is very simple: The value we define in the Result Set Variable Name will simply become a PHP variable we can use anywhere PHP tokens or raw values are accepted.

So for example, is the Result Set Variable Name is the default $fb_query, we could use the token ${fb_query}, or the raw PHP variable $fb_query.

This is an important distinction, because unlike database result sets which are indexed as arrays, this is always a one-off value.

Finally, this mode doesn't work when MySQL 4.1 Compatibility Mode is checked.

Run In Page Top

This checkbox gives you the ability to define where in the page flow this query will run. This is important because in some instances you will not have the required variables to run the query until some other action has taken place, such as when you need to initiate a separate action in the PHP Top Code Block, or inline in your page html code.

By default this is unchecked, and thus the query will appear inline with your page HTML form code, in the order it was placed in the form, or later arranged while in Sortable mode.

If checked, the query code is moved into the head of the page, before any HTML code, but after the PHP Top Code block. This means you could use the PHP Top Code block to query any session variables etc, then use these values to populate any parameters for the query.

To retrieve your SQL result you can use the PHP Head Code, PHP Header Template Code, or PHP Footer Template Code areas.

Finally, unlike Form and Builder page types, if you use a Query module in a confirmation page it always executes inline and in order with any other confirmation modules, it never runs inline with your HTML like it can with Form and Builder pages.

MySQL 4.1 Compatibility Mode

Checking this box means the php_mysql extension will be used to submit your SQL query rather than the standard pdo_mysql or php_sqlsrv_xx. This has the benefit of being MySQL 4.1 compliant. Please be sure you have this extension (php_mysql) loaded before using this mode.

In order to use this mode, you will have format your query parameters to work with the sprintf php function. For example, a call with four parameters would be:

INSERT INTO fb_sample (id,name, manual_ts,manual_dt) VALUES ('%d', '%s', '%s', '%s')

Notice that instead of question marks, each parameter must be represented by a type specifier, the %d above meaning treat the variable as an integer, the %s meaning string. As per the example, you must also wrap each of the type specifier's with tick marks ''.

You pass parameters the same as without MySQL 4.1 Compatibility Mode, with each field id or TIMESTAMP keyword being separated by a comma, as in:

id,name,TIMESTAMP,NOW()

IMPORTANT NOTES: Unlike the non 4.1 compatible mode which returns all rows of a result into an array, your results must be iterated through via a loop. However, the first row of a result is returned via:

$fb_query = mysql_fetch_array($result, MYSQL_BOTH);

Which means for one-row queries you can use:

$fb_query['field_name'];

Also, when this mode is checked you cannot use the Return lastInsertId() feature, as that is only supported in MySQL 5 queries.

Append Label

Along with this item, the next two 'Append' items are new to Build 673, are were designed to give you greater control over the results returned and how you can use them. The basic idea is that when you query the database it could be useful to append a value to the front or back of the result set.

One such use is in Builder forms where we want to add an 'Any' option to a Select item for a search field. Due to the nature of how searching works, when we have Relaxed Search set for the Builder options, any time a search field is passed in using a blank value that field is ignored, which means we will not restrict results based on that field. Hence, we get 'all' results back if the user selects the 'All' option.

These fields then are broken into two main parts: the label and value.

The label, this field, is what would be set as the label of the form field. Technically speaking, this is the first index of the array (the [0] position)

Append Value

Similar to Append Label, this is the value or as far as the raw array is concerned, the [1] position.

Append Position

The last option for appending values is the append position. We have three choices; do not append, append to the front of the array, and append to the back.

Code To Run After Query

This box lets you specify the code to run after the main query. This is particularly handy for things like logging transactions, setting return values, or any other operation that is logically tied to the previous database query.

Please note as this query takes place in the same code block as the query, you do not need to add PHP start and close tags.

Also, this block does not except tokens at this time. Thus, any variables you want to use from elsewhere in your page you must create as local PHP variables first.