[EHPweb] Safe SQL

[Scott Haefner]

Eric,

It is installed in /template/static/functions.inc.php.

Scott

On Nov 4, 2008, at 10:38 AM, Eric M Martinez wrote:

> Scott,
> 	Cool, looks like a useful function. Is this in the generic  
> functions.inc.php?  If not maybe it could be moved there.  Also, you  
> may want to share this with the web team email list.
>
> Thanks,
> 	~Eric.
>
>
>
>
> On Nov 4, 2008, at 11:23 AM, Scott Haefner wrote:
>
>> Hi Eric,
>>
>> I added a function in functions.inc.php that you can call to  
>> cleanse any kind of input at the time of insert into the db. It  
>> also takes care of quotes and assigns NULL to empty strings. The  
>> HTML attributes cleaner is buggy and needs work, but I can't seem  
>> to track down a regex that works reliably. Are you good enough with  
>> regex to write one that works?
>>
>> Scott
>>
>> ---
>>
>> function sqlString ($value, $type = "text", $definedValue = "",  
>> $notDefinedValue = "") {
>>   if (get_magic_quotes_gpc()) stripslashes($value);
>>   if ($type != 'html' && $type != 'xml') $value = strip_tags($value);
>>   $value = mysql_real_escape_string($value);
>>
>>   switch ($type) {
>>     case "text":
>>       $value = ($value != "") ? "'" . $value . "'" : "NULL";
>>       break;		
>>     case "long":
>>     case "int":
>>       $value = ($value != "") ? intval($value) : "NULL";
>>       break;
>>     case "double":
>>       $value = ($value != "") ? "'" . doubleval($value) . "'" :  
>> "NULL";
>>       break;
>>     case "date":
>>       $value = ($value != "") ? "'" . $value . "'" : "NULL";
>>       break;
>>     case "float":
>>       $value = ($value != "") ? floatval($value) : "NULL";
>>       break;
>>     case "defined":
>>       $value = ($value != "") ? $definedValue : $notDefinedValue;
>>       break;
>>     case "xml":
>>       $value = ($value != "") ? "'" . $value . "'" : "NULL";
>>       break;		
>>     case "html":
>>       //1. strip disallowed HTML tags
>>       $value = ($value != "") ? "'" . strip_tags($value,  
>> "<a><strong><em><ul><ol><li>") . "'" : "NULL";
>>
>>       //2. remove all attributes except href
>>       $value = eregi_replace ("<a[^>]+href *= *([^ ]+)[^>]*>", "<a  
>> href=\\1>", $value);
>>       $value = eregi_replace ("<(strong|em|ul|ol|li)[^>]*>", "<\ 
>> \1>", $value);
>>       break;
>>   }
>>   return $value;
>> }
>>
>> On Nov 4, 2008, at 10:10 AM, Eric M Martinez wrote:
>>
>>> All,
>>> 	As I am closely perusing essentially the entire website during  
>>> the migration to SVN I've noticed some troubling practices  
>>> regarding SQL.  This involves neither cleansing inputs or outputs  
>>> to/from the database.  Failure to do so can result in SQL  
>>> injection and cross-site scripting (XSS).  While the latter is  
>>> undesirable , the former can potentially expose us to intentional  
>>> and/or accidental loss of data integrity.
>>>
>>> 	The solution to this is to cleanse all inputs before inserting  
>>> into a database or echoing values to a user.  Since any reads done  
>>> from the database should now be reading cleansed input, you can  
>>> safely echo this directly to the user, however if echoing user- 
>>> input directly from a form, you should certainly make sure to  
>>> cleanse it before doing so.
>>>
>>> 	Cleansing input can be done in a variety of ways...
>>>
>>> ... for MySQL integers use the PHP function "intval"
>>> ... for MySQL decimals use the PHP function "floatval"
>>> ... for MySQL strings use the PHP function  
>>> "mysql_real_escape_string"
>>> ... for content to be echoed to the user (in a browser) use the  
>>> PHP function "htmlspecialchars"
>>>
>>> There are a variety of other functions that may also be useful.  
>>> Read the PHP documentation for more information.  You can check  
>>> out this following example page I created that highlights these  
>>> vulnerabilities in an interactive fashion at:
>>>
>>> 	http://ehpd-earthquake.cr.usgs.gov/unsafe.php
>>> 	(This isn't an awesome page, but just something I threw together  
>>> quickly as an example.)
>>>
>>> Moral of the story? Be careful when working with users.  Cleanse  
>>> your inputs.
>>>
>>> Thanks,
>>> 	~Eric.
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> EHPweb mailing list
>>> EHPweb at geohazards.usgs.gov
>>> https://geohazards.usgs.gov/mailman/listinfo/ehpweb
>>
>