asked on

Handling apostrophes when generating SQL inserts

Developing some SQL-generator code to generate a file of INSERT statements from an SQL Server db (code snippet shown below).

This code works fine, and we end up with a generated file of INSERT statements, but have just encountered a problem in trying to run the generated SQL file against a test database.

using (SqlConnection connection = new SqlConnection(connectionStrin
{
       using (SqlCommand command = new SqlCommand())
       {
       // Initialize the SQL Connection
       command.Connection = connection;
       command.CommandText = "SELECT * FROM " + tableName;
connection.Open();

using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
// Format the first part of the INSERT statement. This part remains
// constant for every row that is generated.
string sInsert = string.Format("INSERT INTO [{0}] ( ", tableName);

// Loop through all the column names
for (int iCol = 0; iCol < reader.FieldCount; iCol++)
{
sInsert += string.Format("[{0}]", reader.GetName(iCol));
if (iCol < reader.FieldCount - 1)
sInsert += ",";
}

// Start outputting the data values for those columns
sInsert += ") VALUES ({0})";

// Read each row of the table
object[] objs = new object[reader.FieldCount];
int nLines = 1;

while (reader.Read())
{
int n = reader.GetValues(objs);
string sValues = string.Empty;

// Go through each column of the row, and generate a string

for (int i = 0; i < n; i++)
{
try
{
string sVal = (reader.IsDBNull(i)) ? "null" : ObjectToSQLString(objs[i]);
sValues += sVal;
if (i < n - 1)
sValues += ",";
}
catch (DataException)
{
Console.WriteLine(string.Format("Conversion error in Record {0}, Column {1}", nLines, reader.GetName(i) ));
return;
}
}

// Dump the INSERT statement to the file
outputStream.WriteLine(string.Format(sInsert, sValues));
nLines++;
}
}

}
}

static string ObjectToSQLString(object o)
{
if (o == null || o == DBNull.Value)
return "null";

Type t = o.GetType();

if (t == typeof (string))
return string.Format("'{0}'", ((string) o).Trim());
if (t == typeof (int))
return ((int) o).ToString();

if (t == typeof (Int16))
return ((Int16) o).ToString();
if (t == typeof (long))
return ((long) o).ToString();
if (t == typeof (float))
return ((float) o).ToString();
if (t == typeof (double))
return ((double) o).ToString();
if (t == typeof (bool))
return ((bool) o).ToString();
if (t == typeof (DateTime))
return string.Format("'{0}'", ((DateTime) o));

throw new DataException();
}

This code works fine, and we end up with a generated file of INSERT statements, but have just encountered a problem
in trying to run the generated SQL file against a test database. The cutdown generated SQL file below illustrates
the problem :

INSERT INTO [customercopy] ( [customer_no],[customer_name]) VALUES (4217,'REGINALD COLIN O'REILLY')

and the problem is where the customer_name value contains an apostrophe (O'REILLY in tyhis case) - as that INSERT statement would fail.

Is there any way of amending my SQL-generator code to address this issue where name fields on the sourtce database might contain apostrophes ?

Thanks

Ray

MikaelHenriksson

The only way I know of is to do a replace on single quotes before insert. Or parameterize the insert statements but that's probably not what you want to hear :)

BrandonGalderisi

Well you should be building parameterized insert statements (or procedure calls) and not building inline SQL. But if you insist, you can replace a single quote with two single quotes this escaping the original one. It will then insert one single quote into the DB.

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ritz93

Use the Replace string to replace ' with \' before making it a part of the string.

Not so sure of the exact syntax but the format may be like Replace(Valuestr, "'", "\''") for SQL or valStr= fieldVal.Text.Replace("'", "\''").

Arragorn

Hello raymurphy,

you could search sval and replace apsotropes with another character of your choosing

see code snippet

Regards,

Arragorn

try
{
     string sVal = (reader.IsDBNull(i)) ? "null" : ObjectToSQLString(objs[i]);
     sVal = Regex.Replace(sVal, "'", " ");
     sValues += sVal;
     if (i < n - 1)
         sValues += ","; 
} 
.....

Open in new window

BrandonGalderisi

Why would you replace it with another character? O'Connor isn't valid as O Connor.

BrandonGalderisi

Besides, with parameterized SQL you don't need to escape ANYTHING. You just have to sanitize your input to prevent about SQL Injection attacks.

raymurphy

ASKER

Points about building parameterized insert statements (or procedure calls) are noted and obviously valid, however the specific requirement here is to produce a generated file of TSQL statements - the generated file will eventually be transmitted to another server, to be inserted into another database (reasons for this approach are long-winded and too drawn out to go into here).

So thanks for all comments, but the solution from emoreau suits my immediate purposes and has therefore been taken as the accepted solution.