[prev in list] [next in list] [prev in thread] [next in thread] 

List:       squirrelmail-cvs
Subject:    [SM-CVS] CVS: squirrelmail/contrib flat2sql.pl,1.5,1.6
From:       Tomas Kuliavas <tokul () users ! sourceforge ! net>
Date:       2005-11-25 15:15:55
Message-ID: E1EffIp-0008HP-JI () sc8-pr-cvs1 ! sourceforge ! net
[Download RAW message or body]

Update of /cvsroot/squirrelmail/squirrelmail/contrib
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv31735

Modified Files:
	flat2sql.pl 
Log Message:
adding initial sql sanitizing code for testing.


Index: flat2sql.pl
===================================================================
RCS file: /cvsroot/squirrelmail/squirrelmail/contrib/flat2sql.pl,v
retrieving revision 1.5
retrieving revision 1.6
diff -u -w -r1.5 -r1.6
--- flat2sql.pl	20 Sep 2005 16:41:28 -0000	1.5
+++ flat2sql.pl	25 Nov 2005 15:15:53 -0000	1.6
@@ -16,6 +16,7 @@
 $db = "squirrelmail";
 $abook_table = "address";
 $pref_table = "userprefs";
+$dbtype = 'mysql';
 ##### ##### #####
 
 use Getopt::Long;
@@ -81,7 +82,7 @@
 # Process a user address file
 
 sub abook {
-  print "DELETE FROM $db.$abook_table WHERE owner = '$username';\n"
+  print "DELETE FROM $db.$abook_table WHERE owner = \
'".escape_sql_string($username,true)."';\n"  if ( defined $opts{delete} );
 
   open(ABOOK, "<$data_dir/$filename") or 
@@ -94,18 +95,22 @@
 
     print "INSERT INTO $db.$abook_table "
         . "(owner,nickname,firstname,lastname,email,label) "
-        . "VALUES ('$username','$nickname','$firstname','$lastname',"
-        . "'$email','$label');\n"; 
+        . "VALUES ('"
+        .escape_sql_string($username)."','"
+        .escape_sql_string($nickname)."','"
+        .escape_sql_string($firstname)."','"
+        .escape_sql_string($lastname)."','"
+        .escape_sql_string($email)."','"
+        .escape_sql_string($label)."');\n"; 
   }
 
   close(ABOOK);
 }
 
-# Process a user prefernce file
-
+# Process a user preference file
 sub pref {
   print "DELETE FROM $db.$pref_table "
-    . "WHERE user = '$username' and prefkey not like '___sig\%___';\n"
+    . "WHERE user = '".escape_sql_string($username,true)."' and prefkey not like \
'___sig\%___';\n"  if ( defined $opts{delete} );
 
   open(PREFS, "<$data_dir/$filename") or 
@@ -118,21 +123,23 @@
 
     print "INSERT INTO $db.$pref_table "
         . "(user,prefkey,prefval) "
-        . "VALUES ('$username','$prefkey','$prefval');\n"; 
+        . "VALUES ('"
+        .escape_sql_string($username)."','"
+        .escape_sql_string($prefkey)."','"
+        .escape_sql_string($prefval)."');\n"; 
 
   }
 
   close(PREFS);
 }
 
-# Process a user sig file
-
+# Process a user signature file
 sub sig {
 
   $del_ext = $1;  
   $del_ext = "nature" if ( $del_ext eq "g" );
   print "DELETE FROM $db.$pref_table "
-    . "WHERE user = '$username' and prefkey like '___sig" . $del_ext . "___';\n"
+    . "WHERE user = '".escape_sql_string($username,true)."' and prefkey like \
'___sig" . escape_sql_string($del_ext,true) . "___';\n"  if ( defined $opts{delete} \
);  
   open(SIG, "<$data_dir/$filename") or 
@@ -150,11 +157,45 @@
   }
 
   print "INSERT INTO $db.$pref_table (user,prefkey,prefval) "
-    . "VALUES ('$username','$prefkey','".join("", @lines)."');\n";
+     . "VALUES ('".escape_sql_string($username)."','"
+     .escape_sql_string($prefkey)."','"
+     .escape_sql_string(join("", @lines))."');\n";
+}
+
+# Escapes sql strings
+# MySQL escaping:
+#  http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html
+#  full - \x00 (null), \n, \r, \, ', " and \x1a (Control-Z) 
+#         add % and _ in pattern matching expressions. 
+#  short - only character used for quoting and backslash should be escaped
+# PostgreSQL
+# Oracle
+# Sybase - different quoting of '
+sub escape_sql_string() {
+  my ($str,$isPattern) = @_;
+
+  if ($dbtype eq 'mysql'){
+    # escape \, ' and "
+    $str =~ s/(['"\\])/\\$1/g;
+    # escape \x1a
+    $str =~ s/([\x1a])/\\Z/g;
+    # escape ascii null
+    $str =~ s/([\x0])/\\0/g;
+    # escape line feed
+    $str =~ s/([\n])/\\n/g;
+    # escape cr
+    $str =~ s/([\r])/\\r/g;
+    if ($isPattern) {
+      $str =~ s/([%_])/\\$1/g;
+    }
+  } else {
+    die "ERROR: Unsupported database type";
+  }
+  return $str;
 }
 
-# Print out the usage screen
 
+# Print out the usage screen
 sub Usage {
 
 $0 =~ /.*\/(.*)/;



-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems?  Stop!  Download the new AJAX search engine that makes
searching your log files as easy as surfing the  web.  DOWNLOAD SPLUNK!
http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click
--
squirrelmail-cvs mailing list
List Address: squirrelmail-cvs@lists.sourceforge.net
List Info: https://lists.sourceforge.net/lists/listinfo/squirrelmail-cvs
http://squirrelmail.org/cvs


[prev in list] [next in list] [prev in thread] [next in thread]