CODEWITHSUNDEEP
c#.netsqldatabase
Ankush Jain
Ankush Jain

Reputation: 1527

SQL Query with single quotes

getting Error while executing this query , because column text may contain text with single quotes also. How can i use this query w/o any error My code is

public bool updateCMStable(int id, string columnName, string columnText)
{
    try
    {
        string sql = "UPDATE  CMStable SET " + columnName + 
                      "='" + columnText + "' WHERE cmsID=" + id;
        int i = SqlHelper.ExecuteNonQuery(Connection.ConnectionString,
                                          CommandType.Text,
                                          sql);
        if (i > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    catch (Exception ee)
    {
        throw ee;
    }
}

Upvotes: 1

Views: 6329

Answers (6)

Rizwan
Rizwan

Reputation: 69

public bool updateCMStable(int id, string columnName, string columnText)
            {
                 try
                {
string sql = "UPDATE  CMStable SET '"+columnName+"' = '"+columnText+"' where cmdID='"+id+"'";
int i = SqlHelper.ExecuteNonQuery(Connection.ConnectionString,CommandType.Text,sql);
        if (i > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    catch (Exception ee)
    {
        throw ee;
    }
}

Upvotes: 0

AnandPhadke
AnandPhadke

Reputation: 13486

Try this:

public bool updateCMStable(int id, string columnName, string columnText)
{
    try
    {
        columnText=columnText.Replace("'","''")
        string sql = "UPDATE  CMStable SET " + columnName + 
                      "='" + columnText + "' WHERE cmsID=" + id;
        int i = SqlHelper.ExecuteNonQuery(Connection.ConnectionString,
                                          CommandType.Text,
                                          sql);
        if (i > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    catch (Exception ee)
    {
        throw ee;
    }
}

Upvotes: 0

Chris Gessler
Chris Gessler

Reputation: 23113

To fix your code, escape all single quotes with an additional single quote. However I agree with Oded... you need to be using a parameterized query, or possibly a stored proc.

public bool updateCMStable(int id, string columnName, string columnText) 
{ 
   if(!string.IsNullOrEmpty))
   {
       switch(columnName)
       {
           // TODO: change 50 & 100 to the real sizes of your columns, 
           // and obviously the column names too...
           case "column1":
               if(columnText.Length > 50)
                   columnText = columnText.SubString(0, 50);
               break;
           case "column2":
               if(columnText.Length > 100)
                   columnText = columnText.SubString(0, 100);
               break;
           etc... 
        }
    }
    // replace single quote with double single quotes
    columnText = columnText.Replace("'", "''");
    string sql = string.Format("UPDATE CMStable SET {0} = '{1}' WHERE cmsID={2}", 
        columnName, 
        columnText, 
        id); 
    int i = SqlHelper.ExecuteNonQuery(Connection.ConnectionString, CommandType.Text, sql); 
    return (i > 0); 
}

I made some additional corrections to your code.

  1. You can simply return the result of an if statement when you're returning true | false
  2. You don't need to catch exceptions if you're simply going to throw it in the catch block
  3. If you DO catch an exception, do something meaningful with it, and decide to rethrow it, use throw by itself, or you'll reset the stack trace. Don't use throw ee;
  4. Replace + type concatination with string.Format if it becomes too unreadable.

Edit:

The error you posted is happening because the length of the data being passed in is longer than the specified length of the column. Since you're using dynamic SQL, the only way around it that I can see is to use a case statement. Each field may have a different size, or maybe not, but the string will have to be truncated to fit to avoid the error. If all the field sizes are the same, you won't need the case statement.

Upvotes: 0

Nathan
Nathan

Reputation: 6216

Use LinqToSql. Please....... there are lots of reasons why writing code as you've listed above is not a good idea - security, design, sanity....

Google LinqToSql and "Repository Pattern" as a basic starting point for writing maintainable, useful data interactions.

Upvotes: 0

yogi
yogi

Reputation: 19591

The error was

"String or binary data would be truncated. The statement has been terminated"

Main cause of this error is the shorter length of the column in which you are trying to save any value, suppose if your column is of varchar(100) type and you try to save a string of 105 characters in it then you'll get this error.

Upvotes: 0

Oded
Oded

Reputation: 498904

Instead of constructing your SQL in a string you should use parameterized queries - your current code, apart from having an issue with single quotes is vulnerable to SQL Injection.

There is a limitation in that you are trying to use a dynamic column name which cannot be parameterized, but you can still use dynamic SQL in a safer manner.

I suggest reading The Curse and Blessings of Dynamic SQL by Erland Sommarskog for a comprehensive treatment of the subject.

Upvotes: 5

Related Questions