escaping a text field

[danielf]

I'm trying to keep track of user edits to records. In order to do so I have separate screens for viewing and editing a record. The screen that is used for editing a record updates the main record in the appropriate table.

In the after save section of the edit screen I read the fields that were edited and create a new record which stores the identifier for the record that was edited, the field that was edited, and stick these in a separate 'edits table'. This all works as I want it to, provided there are no special characters (e.g. apostrophes) in a text field that is read. Nubuilder's save option obviously takes care of them when updating the primary record, but since I'm creating a new record in the after save section, I need to escape the special characters manually. Looking at mysql as well as the php code for nubuilder, I've come across mysql_real_escape_string() and addEscapes(), but I've not been able to get this to work. Assuming the edited field is customer_name, and customer_id is the edited record, I'm thinking I need to do something like:

Code: Select all

$record_id = '#customer_id#';
$new_id = uniqId(1);
$the_edited_text = mysql_real_escape_string('#customer_name#'');

$query = "INSERT INTO edits SET edit_id = '$new_id', edited_record = '$record_id', edit_text = '$the_edited_text'";
nuRunQuery($query);

But it's not working. The basic idea works, but the code chokes on several variants of the following line if there's an apostrophe or other special character in the text field.

$the_edited_text = mysql_real_escape_string('#customer_name#'');

I've also tried reading the edited field from the data base, but run into the same problems if there are special characters in the field. So how do I escape special characters (including the hash character presumably) when reading a field? Or am I making things needlessly complex when there are far easier options to keep track of changes to records?

Thanks,

Dan.

[admin]

Dan,

How about trying this for each value before inserting it into your sql string.

Code: Select all

function escapeBSandSQ($pValue){

//-- escape firstly backslashes and then single quotes

   $bs = '\';
   return str_replace("'","\'",str_replace($bs,$bs.$bs, $pValue))

}

Steven

[danielf]

Thanks Steven,

If I'm not mistaken, that function is essentially addescapes from common.php.

I stuck your code in the code library where I was unable to save it unless adding a semicolon to the str_replace line and changing $bs = '/'; to $bs = '//'; (as in addescapes).

Either way, addEscapes, mysql_real_escape_string and your (amended) code all behave in the same way. They work alright (i.e. the entire script is executed) when there are no characters that need escaping, thus suggesting there is nothing wrong with the rest of the script. However, all three trip up when there are escape characters in the string that is read/converted. Thus, in my particular case they don't appear to be very good at what they're supposed to do, and I'm at a loss as to why this might be the case.

Likewise, if I place the following in the before save section (thus bypassing any other code).

Code: Select all

  $escaped_text = mysql_real_escape_string('#action_text#');
  nuDebug($escaped_text);

It will print the 'action_text' field to the zzsys_trap only if there are no special characters in the text field.

I do get an error message thrown up on the nuBuilder form when working on text with special characters, but it's gone so quickly that it's very hard to read. I think it's complaining about an unexpected T-string.

Edit: Oh! I cracked it!

I was going to try and replicate my problem in sampledebtors, and noticed that the add screen in customers reads the fields not as:

$text = '#cus_name#'

But as:
$text = $_POST["cus_name"];

So I used that instead, and next ran the variable through mysql_real_escape_string before sticking it into the SQL statement, and now it works...

So it seems that reading a text field through a hash variable is not a very safe thing to do, and $_POST["whatever"] whatever is a cleaner way of reading the data. Not sure why, but I'm very pleased it works. Time for the weekend...

[admin]

.