[daBoomKing]

Hi All

I'm not really sure if I need to worry about preventing SQL injection or if that's already taken care of by the native PHP code? I have third party users on the system and I want to make sure its being kept as secure as possible.

Thanks a lot
Andrew

[amosbatto]

I looked into the code to see what is happening with dependent fields. If you reference a field as @#fieldID or @=fieldID in your SQL query, then you have no protection. If you reference it as @@fieldID, then the problematic characters which can be used in SQL injection attacks are escaped with \ (backslash) and all \ in the text are stripped. (In my opinion, this is not correct because \ should become \\, but stripping \ makes it safe.)

If you set the value of a field with JavaScript to "\n,\r,\0,',\",\\,\x1a,\xc8" (which are the problematic characters in MySQL), then the query in MySQL will be "\n,\r,\0,\',\",,\Z,É" (note that \ is stripped).

If needing to use an integer or a decimal number in an SQL query, then reference the field as @@fieldID and use the CAST() function to convert to an integer or decimal, like this:

Code: Select all

SELECT X, Y FROM Z WHERE AMOUNT > CAST(@@amount AS DECIMAL(10,2)) OR 
QUANTITY <= CAST(@@quantity AS UNSIGNED)

If using executeQuery() in a trigger and using data from a Dynaform field which is entered by the user, then you need to pass that data through a function like mysql_real_escape_string() for MySQL databases:

Code: Select all

$var1 = mysql_real_escape_string(@@var1); 
$sql = "SELECT X, Y FROM Z WHERE A='$var1'";
executeQuery($sql);

This code will work for PM Tables. It will also work for external MySQL databases, which also use the UTF-8 character just like ProcessMaker's wf_<WORKSPACE> database. If they use another character set, then you need to login to that database so mysql_real_escape_string() knows which character set your database is using.

For other databases, you can use addslashes() instead of mysql_real_escape_string(), but it doesn't provide complete protection. For example, if using MS SQL Server, see this:
http://stackoverflow.com/questions/5748 ... -using-php

The other solution is to use PDO for your database queries in triggers.

By the way, I have filed a bug report asking for a PMFEscape() function that escapes strings using the correct character set for the database connection, so this should eventually get fixed.

[daBoomKing]

Hi Amosbatto

Thanks a lot, appreciate the info. For me, its a lot to take in as I manage the processmaker as someone that's not a developer. With that in mind, I'm not sure I ever saw anything in the documentation about managing security. I had thought that the focus on making developing processes simple meant that security was being handled from within the core of PM.

I wonder how many people are unaware of the requirements to handle this themselves? Is security explained in the official docs anywhere?

Kind Regards
Andrew

[amosbatto]

There is no document explaining this, although we do have a warning about needing to use mysql_real_escape_string() with executeQuery(). Basically, I had to dig through the code to figure out what ProcessMaker is doing. All of this will be fixed when PM switches to a new version of Propel, but I don't expect that to happen for another year.