Archive for the ‘MySQL’ Category

PHP Wildcard Match in an Array

Thursday, November 30th, 2006

I was working with a list of wildcard banned domain names (e.g. *.spamsite.com) stored in a database. I needed to compare a submitted domain (e.g. anything.spamsite.com) to the wildcard entries stored in the database.

Essentially then I need the function in_array to perform with a wildcard match with the wildcards being the array haystack not the needle.

In the above example, I need the script to detect that anything.spamsite.com is banned. Here’s what I did:

$bannedurl = false;

//The function fnmatch is useful here but is not available on Windows systems, so it must be defined if it does not exist.

   if (!function_exists('fnmatch')) {
       function fnmatch($pattern, $string) {
        return @preg_match('/^' . strtr(addcslashes($pattern, '.+^$(){}=!<>|'), array('*' => '.*', '?' => '.?')) . '$/i', $string);
       }
   }

//This function allows wildcards in the array to be searched
   function my_inArray($needle, $haystack) {
      foreach ($haystack as $value) {
       if (true === fnmatch($value, $needle)) {
        return true;
       }
      }
      return false;
   }
   foreach ($wild_urls as $value) {
    $wild_urls[] = '*' . $value;
   }

//An Example

   $wild_urls = array('*.spamsite.com');
   $domain = 'anything.spamsite.com';

//Test it, returns true if the Domain is banned.

   if (my_inArray($domain, $wild_urls)) $bannedurl = true;

//Note, for the domain spamsite.com, you can do a straight MySQL query such as " ...... where domain like '%" . $domain . "' and domain not like '%_." . $domain . "' "

MySQL Injection Attacks

Monday, November 27th, 2006

MySQL injection attacks occur when the code of a MySQL query can be altered by the user due to improper escaping of variables.

See http://us3.php.net/mysql_real_escape_string for a  great example of an SQL injection attack.

MySQL injection attacks can be prevented by using mysql_real_escape_string($mystring) for each variable inputted into a MySQL query.

Find and Replace with MySQL

Monday, November 27th, 2006

If you’re just trying to find a replace a string in a MySQL table, you don’t need to write a script to do it. You can simply use the following syntax with MySQL (e.g. phpMyAdmin, MySQL shell access)

UPDATE TABLENAME SET COLUMNNAME = replace(COLUMNNAME,”FINDWHAT”,”REPLACEWITHWHAT”);Note that this method is case-sensitive. This is a great timesaver over doing it with a scripting language such as PHP. With PHP, you’d have to write a script to select the row, do the replacement, and update the table.