alexcolson.com alexcolson.com [Printable Version]
MySQL Prep
Home > Tech Talk > Article

[Create New Article] :: [Browse Tags]

Modified: Sunday, September 23, 2007 10:26:35 PM by Alex Colson
Tags: MySQL, Perl, SQL, Web Development

MySQL prep makes user input safe to use in SQL queries.  MySQL prep should be used whenever user input is piped directly into the SQL query to prevent SQL Injection.

The code below is written in Perl but the same concepts apply to any language.

The code does the following:
  1. Removes whitespace at the beginning and end of the string.
  2. Replaces backslashes (\) with double backslash (\\).
  3. Replaces single quotes (') with backslashed single quote (\').
  4. Replaces percent signs (%) with backslashed percent signs (\%) to prevent wildcard errors with MySQL.
  5. Removes all charactors that are not alpha-numeric, whitespace, or special charactors.
sub mysqlprep {
  local ($string) = @_;
  $string =~ s/^\s+//g;
  $string =~ s/\s+$//g;
  $string =~ s/\\/\\\\/g;
  $string =~ s/\'/\\\'/g;
  $string =~ s/\%/\\\%/g;
  $string =~ s/[^\w\s\~\`\!\@\#\$\%\^\&\*\(\)\+\-\=\[\]\\\{\}\|\;\'\"\:\,\.\/\?\<\>]//g;
  return $string;
}
Sunday, September 5, 2010 10:39:08 AM EST (0.0143 s) Copyright © 2008 Alex Colson