[Mono-list] escaping a string for sql

[Michael J. Ryan]

This is a multi-part message in MIME format.
--------------040202000707080507080201
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

Howard Cole wrote:
> For postgres, you also need to replace "\"
> 
>    public string escape (string s)
>    {
>      s = s.Replace("\\", "\\\\"); // Replace \ with \\
>      s = s.Replace("\'", "\'\'"); // Replace ' with '' (SQL Standard)
>      //s = s.Replace("\"", "\\\""); not necessary if enclosed in single 
> quotes.
>      return s;
>    }

here's mine, which is part of a postgre util I am using.. :)  probably the
most usefull would be the ConvertTo.Sql(string) and ConvertTo.Sql(Guid),
it should be pretty usable as-is, I'm using BYTEA to store System.Guid ...
was a bit of a pita to get the encoding right, but it works smoothly, the
main purpose of the ConvertTo.Sql() method overloads, is for simple
queries, especially (for me) queries with a guid as a key field, which runs
pretty smooth from what I've done so far.  Thinking on doing an article
focused on using .Net with Npgsql + Postgre 8 ...

The only real gotchas I've come accross so far are dealing with the guid
encoding, and haven't worked with some of the array values coming out of
postgre (haven't needed them)...

So far I haven't really tested it all, only bits and pieces, it's a conversion 
from a mysql util I was working on.. getting ready to start using it in the AM 
for a new project.

If someone has a better method of escaping the binary strings, let me know,
(yes, I know about the conversion methods under the Microsoft.VisualBasic 
namespace, but I wanted to avoid using it)

-- 
Michael J. Ryan - tracker1(at)theroughnecks(dot)com - www.theroughnecks.net
icq: 4935386  -  AIM/AOL: azTracker1  -  Y!: azTracker1  -  MSN/Win: (email)

--------------040202000707080507080201
Content-Type: text/plain;
 name="Roughneck.PostgreUtilitiy.DB.cs"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="Roughneck.PostgreUtilitiy.DB.cs"

/*******************************************************************************
Some DB Utility Functions for Postgre
by Michael J. Ryan (tracker1 - at - theroughnecks.com)
*******************************************************************************/
using System;
using System.Configuration;
using System.Data;
using System.Text;
using Npgsql;
using NpgsqlTypes;

namespace Roughneck.PostgreUtility {
	public class DB {
		private DB() { /* Operates as a static singleton */ }
		
		public static int Exec(string query) {
			NpgsqlConnection cn = new NpgsqlConnection(ConnectionString);
			cn.Open();
			int ret =  Exec(query, cn);
			cn.Close();
			cn = null;
			return ret;
		}
		
		public static int Exec(string query, NpgsqlConnection cn) {
			NpgsqlCommand cmd = new NpgsqlCommand(query, cn);
			return cmd.ExecuteNonQuery();
		}
		
		public static int Exec(string query, NpgsqlConnection cn, NpgsqlTransaction tx) {
			NpgsqlCommand cmd = new NpgsqlCommand(query, cn, tx);
			return cmd.ExecuteNonQuery();
		}
		
		public static DataTable GetTable(string query) {
			DataTable ret = new DataTable();
			
			NpgsqlConnection cn = new NpgsqlConnection(ConnectionString);
			NpgsqlDataAdapter dad = new NpgsqlDataAdapter(query, cn);
			cn.Open();
			dad.Fill(ret);
			cn.Close();
			
			return ret;
		}
		
		public static DataTable GetTable(string query, NpgsqlConnection cn) {
			DataTable ret = new DataTable();
			
			NpgsqlDataAdapter dad = new NpgsqlDataAdapter(query, cn);
			dad.Fill(ret);
			
			return ret;
		}
		
		public static DataTable GetTable(string query, NpgsqlConnection cn, NpgsqlTransaction tx) {
			DataTable ret = new DataTable();
			
			NpgsqlCommand		cmd	= new NpgsqlCommand(query, cn, tx);
			NpgsqlDataAdapter	dad	= new NpgsqlDataAdapter(cmd);
			dad.Fill(ret);
			
			return ret;
		}
		
		public static string GetSelect(string tablename, string[] fields, string whereClause) {
			return GetSelect(tablename, fields) + "\r\n " + whereClause;
		}
		
		public static string GetSelect(string tablename, string[] fields) {
			StringBuilder query = new StringBuilder(1000);
			query.Append("SELECT ");
			for (int i=0; i< fields.Length; i++)
				query.Append(string.Format("\r\n\t\"{0}\"{1}",fields[i],((fields.Length == i+1)?"":",")));
		
			query.Append(string.Format("\r\nFROM \"{0}\"", tablename));
			             
			return query.ToString();
		}
		
		public static string GetInsertQuery(string tableName, string[] fields) {
				//INSERT
				StringBuilder query = new StringBuilder(1000);
				query.Append(string.Format("INSERT INTO \"{0}\" (",tableName));
					
				for (int i=0; i<fields.Length; i++)
					query.Append(string.Format("\r\n\t\"{0}\"{1}",fields[i],((fields.Length == i+1)?"":",")));
						
				query.Append("\r\n) VALUES (");
				
				for (int i=0; i<fields.Length; i++)
					query.Append(string.Format("\r\n\t @{0} {1}",fields[i].Replace("-",""),((fields.Length == i+1)?"":",")));
				
				query.Append("\r\n)");
				
				return query.ToString();
		}
			
		public static string GetUpdateQuery(string tableName, string[] fields, string keyFieldName) {
			StringBuilder query = new StringBuilder(1000);
			query.Append(string.Format("UPDATE \"{0}\" SET",tableName));
					
			for (int i=0; i<fields.Length; i++)
				if (fields[i].ToLower() != keyFieldName.ToLower())
					query.Append(string.Format("\r\n\t\"{0}\"=@{1}{2}",fields[i],fields[i].Replace("-",""),((fields.Length == i+1)?"":",")));
			
			query.Append(string.Format(
				"\r\nWHERE \"{0}\"=@{0}\r\n",
				keyFieldName
			));
			
			return query.ToString();
		}
		
		public static string GetUpdateQuery(string tableName, string[] fields, string keyFieldName, Guid keyValue) {
			return GetUpdateQuery(tableName, fields, keyFieldName, (object)ConvertTo.Sql(keyValue), true)
		}
			
		public static string GetUpdateQuery(string tableName, string[] fields, string keyFieldName, object keyValue, bool keyIsNumeric) {
			StringBuilder query = new StringBuilder(1000);
			query.Append(string.Format("UPDATE \"{0}\" SET",tableName));
					
			for (int i=0; i<fields.Length; i++)
				query.Append(string.Format("\r\n\t\"{0}\"=@{1}{2}",fields[i],fields[i].Replace("-",""),((fields.Length == i+1)?"":",")));
			
			query.Append(string.Format(
				"\r\nWHERE \"{0}\"={2}{1}{2}\r\n",
				keyFieldName,
				((keyIsNumeric)?keyValue:keyValue.ToString().Replace("'","''")),
				((keyIsNumeric)?"":"'")
			));
			
			return query.ToString();
		}
		
		public static String ConnectionString {
			get { return ConfigurationSettings.AppSettings["ConnectionString"]; }
		}
		
		public class ConvertTo {
			private static string Escape(byte input) {
				int x = (int)input;
				return string.Format("\\\\{0}{1}{2}",((x>>6) & 7),((x>>3) & 7),(x & 7));
			}
			
			private static string Escape(char input) {
					if (input == '\\')
						return "\\134"; //special character escape
					else if (input == '\'')
						return "\\047"; //special character escape
					else if (input >= (char)32 && input <= (char)127)
						return input.ToString(); //us-ascii - leave alone
					else if (input <= (char)byte.MaxValue)
						return Escape((byte)input); //upper/lower ascii char, escape
					else 
						return input.ToString(); //unicode - leave alone
			}
			
			public static Guid Guid(object input) {
				if (DBNull.Value.Equals(input))
					return new Guid();
				else
					return new Guid((byte[])input);
			}
			
			public static bool Boolean(object input) {
				return Boolean(input, false); //default false
			}
			
			public static bool Boolean(object input, bool defaultValue) {
				if (DBNull.Value.Equals(input))
					return defaultValue; //null defaults to false
				
				switch ((input.ToString().ToLower() + ((defaultValue)?"1":"0"))[0]) {
					case '0':
						return false;
					case 'f':
						return false;
					case 'n':
						return false;
					default:
						return true;
				}
			}
			
			public static string Sql(Guid input) {
				return Sql(input.ToByteArray());
			}
			
			public static string Sql(byte[] input) {
				StringBuilder ret = new StringBuilder(input.Length * 5);
				int x;
				for (int i=0; i<input.Length; i++) {
					x = (int)input[i];
					ret.AppendFormat("\\\\{0}{1}{2}",((x>>6) & 7),((x>>3) & 7),(x & 7));
				}
				
				return "'" + ret.ToString() + "'::\"bytea\"";
			}
			
			public static string Sql(bool input) {
				return (input)?"true":"false";
			}
			
			public static string Sql(string input) {
				StringBuilder ret = new StringBuilder(input.Length * 2);
				for (int i=0; i<input.Length; i++) {
					ret.Append(Escape((char)input[i]));
				}
				return "'" + ret.ToString() + "'";
			}
			
			public static string Sql(int input) {
				return input.ToString();
			}
			
			public static string Sql(long input) {
				return input.ToString();
			}
			
			public static string Sql(float input) {
				return input.ToString();
			}
			
			public static string Sql(double input) {
				return input.ToString();
			}
			
			public static string Sql(decimal input) {
				return input.ToString();
			}
		}
	}
}

--------------040202000707080507080201--