Get an escaped multi syntax sql update string

Question

I'm writing an add-in for Enterprise Architect, and I need to manipulate the internal scripts. I would like to add my own function to an existing script.

The scripts are stored in the table t_script in the column Script.

Unfortunately, the scripts are not exposed in the API so I'll have to work around that and use an update query on the database to update the script.

The problem is that scripts tend to use lots of characters that might pose a problem when using them in an sql update query, but I'm not very keen on writing my own escape function.

So what I tried was this

public void addCode(string functionCode)
{
    this._code += functionCode;
    SqlCommand sqlCommand = new SqlCommand("update t_script set script = @functionCode where ScriptID = " + this.scriptID );
    sqlCommand.Parameters.AddWithValue("@functionCode",this._code);
    this.model.executeSQL(sqlCommand.CommandText);
}

I was hoping the sqlCommand.CommandText would give me the actual SQL string that was going to be executed, but it doesn't. It is basically still the same string I created it with and it didn't replace "@functionCode".

An additional difficulty is that my SQL string needs to work on all DBMS types supported by EA

• SQL Server 2000, 2005, 2008 and 2012
• MySQL
• Oracle 9i, 10g, 11g and 12c
• PostgreSQL
• MSDE
• Sybase Adaptive Server Anywhere
• MS Access
• Progress OpenEdge
• Firebird

Does anyone have a better solution than writing my own escape function?

Answer 1

Because I didn't really find some kind of existing functionality I had to resort to writing my own escape function.

This is what I came up with. It seems to work on MS-Access, FireBird, SLQ Server, MySQL and Oracle (I haven't tested the others)

/// <summary>
/// escapes a literal string so it can be inserted using sql
/// </summary>
/// <param name="sqlString">the string to be escaped</param>
/// <returns>the escaped string</returns>
public string escapeSQLString(string sqlString)
{
    string escapedString = sqlString;
    switch ( this.repositoryType) 
    {
        case RepositoryType.MYSQL:
        case RepositoryType.POSTGRES:
            // replace backslash "\" by double backslash "\\"
            escapedString = escapedString.Replace(@"\",@"\\");
        break;
    }
    // ALL DBMS types: replace the single qoutes "'" by double single quotes "''"
    escapedString = escapedString.Replace("'","''");
    return escapedString;
}

EDIT: Fixed the code after following the comments about the backslash escaping