How to Prevent Error with Data that Contains Apostrophe Character in Websites that Generated by PHPMaker 9.0.3

This following trick will show you how to prevent an error while working with data that contains the apostrophe (') character in websites that generated by PHPMaker. Let's say, your user will input data that contains the apostrophe character in one of the fields in a table which has a primary key as an auto-number field. Then, you want to check whether the record that contains the apostrophe character along with the other data in the other fields already exists in database.

For this purpose, then you will use SQL script to check the duplicate records. If the record already exists, then it will be rejected following with the user-friendly failure message "Data already exists in database!" (instead of error PHP message), otherwise it will be saved into the database. If you don't handle the possibility of data that contains apostrophe character, then your web application will raise an error from PHP service. So, here is the trick that you can use in order to prevent the error that came from PHP service.

[hidepost]

1. For simplicity, we are using the terms as following:
   - YourTable is the table name,
   - YourIDField is the auto-number primary key of the table name,
   - FirstField is the first field (varchar) that we want to check before inserting,
   - SecondField is the second field (varchar) that we want to check before inserting,
   - ThirdField is the third field (varchar) that we want to check before inserting.

   In other words, since the primary key of the table is the auto-number field type (YourIDField), then we have to check the duplicate records based on the criterias in some fields --- in this example: FirstField, SecondField, and ThirdField --- (see the SQL in the next step below). Again, this naming rule is only for simplicity. Adjust it according to your needs!

2. Since we are talking about how to prevent an error regarding apostrophe character while inserting record, then we will use Row_Inserting function that located under Server Events -> Table-Specific -> Common -> Row_Inserting. You can use the following code (of course with the adjustment at sufficiently) and insert it into your Row_Inserting function:

   	global $conn; 
   	$FirstField = ($rsold["FirstField"] == $rsnew["FirstField"]) ? str_replace("'", "''", $rsold["FirstField"]) : str_replace("'", "''", $rsnew["FirstField"]);
   	$SecondField = ($rsold["SecondField"] == $rsnew["SecondField"]) ? str_replace("'", "''", $rsold["SecondField"]) : str_replace("'", "''", $rsnew["SecondField"]);
   	$ThirdField = ($rsold["ThirdField"] == $rsnew["ThirdField"]) ? str_replace("'", "''", $rsold["ThirdField"]) : str_replace("'", "''", $rsnew["ThirdField"]);
   	$sSql = "SELECT YourIDField FROM YourTable 
   		WHERE FirstField = '".$FirstField."' 
   		AND SecondField = '".$SecondField."' 
   		AND ThirdField = '".$ThirdField."'"; 
   	$rs = $conn->Execute($sSql);
   	if ($rs->RecordCount() > 0) {
   	  $this->setFailureMessage("Data already exists in database!");      
   	  return FALSE;
   	} else {	  
   	  return TRUE;
   	}
   

3. From that code above, we are using str_replace function in order to replace the single-apostrophe-character with the double-apostrophe-character. This will prevent an error while checking the duplicate records before inserting record to database by using the SQL above. If the duplicate records are found, then the inserting record process will be canceled, and the web application will display the user-friendly failure message: "Data already exists in database!" instead of displaying the PHP error message.

[/hidepost]