Click here to Skip to main content
14,325,981 members
Rate this:
Please Sign up or sign in to vote.
See more:
In my project, we are using a SQL inline query as shown below. The query is throwing an error when a string parameter has a single quote(') value.

var Query = "Update temp set HIBLCK14='" + _billingDetails.Qual + "',HICOND='" + 
             CondRelate + "', HIATLEG='" + _billingDetails.AttendingLegacy + "', 
             HIATT#='" + _billingDetails.AttendingPhys + "', HIATNM='" + 
             _billingDetails.AttendingPhysName + "'" +
             " where HILOCX =" + Locix ;

var status = ExecNonQuery(Query);
         
 
 
public bool ExecNonQuery(string Query)
        {
          try
            {
                using (SqlCommand cmd = new SqlCommand(Query, _conn))
                {
                    if (_conn.State != ConnectionState.Open)
                    {
                        _conn.Open();
                    }
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                _conn.Close();
            }
        }
ERROR:System.FormatException: The SQL statement text is not valid


I know we can handle this error by replacing the single quote(') to double('') for the parameter. But in my project, there are a lot of queries and parameter. To apply replace for each parameter is time-consuming (donkey work) for me.

Please tell me how can I find the single quote parameter from the SQL query and replace.So, I can handle it in "ExecNonQuery()" function and will not need to make change for all parameters(HILBNM.Replace(''','''')). In below query, there is a column "HILBNM" which has a single quote(Children's) value

Update temp set HIBLCK14='',HICOND='', HIATLEG='', HIATT#='', HIATNM='', HIOTHLEG='MA159694',HIOTH#='',HIFST='', HIFZIP=0, HIFZP2=0, HILBNM='Boston Children's Hospita',  HILBAD='PO BOX D-3053',HILBA2='', HILBCT='BOSTON',
HIDIA11 ='',HIDIA12 ='' where HILOCX =18693585 and HISEQ=3 and HIDOS=0


What I have tried:

HILBNM.Replace(''','''')
Posted
Updated 10-Jan-19 0:10am
v3
Rate this:
Please Sign up or sign in to vote.

Solution 1

Print your query in debug window or file, copy the query run from the console. You will have better view of your error.

And also you have lot's of parameter that you are directly concatenating with your query, this is risky, will lead to serious damage to your system. Try look up sql injection.

And finally if you have looked at your own question you would have noticed there an extra quote in your variables,
HILBNM='Boston Children's Hospita'  <<-- Children's


What is this class DB2Helper?
DB2Helper class supposed to give you a binding function find that and use that
   
Comments
Maciej Los 10-Jan-19 2:58am
   
Nice tip!
Mohibur Rashid 10-Jan-19 19:52pm
   
Thanks!
Rate this:
Please Sign up or sign in to vote.

Solution 2

Never create an SQL command by concatenating strings together!.
As you have discovered single quotes can break a statement. And that is the lucky version that your program throws an error. IF someone should craft the values going in then they can "short-circuit" the command and then start running their own.
This was identified as a major vulnerability 20 years ago and is still one of the top 10 ways websites are hacked

The way to avoid this is to use a Parameterized Query. Your original query will have placeholder variables put inline, and then parameters are added to the command

The nice thing about using parameters is you do not need to worry about the quotes (or other identifiers used (such as #date# in some versions of Access). You do need to be careful as the placeholders will be replaced with SQL data typing based on the values being put in; so if you have an INT inside of a textbox it will be formatted as a string; so make sure the values going in are properly typed.
Typically it would look something like this:
SqlCommand cmd = new SqlCommand("UPDATE table SET ColumnValue = @NewValue WHERE TableID = @TableID", _connection);
cmd.Parameters.AddWithValue("@NewValue", somevalue);
cmd.Parameters.AddWithValue("@TableID", indexvalue);


Your case is a little different,as you are passing the CommandText to some sort of helper.
So what we will need to do is to build a collection of some sort that the values can be <string,object> or <object,object>. So are new query will look something like this
var Query = "UPDATE temp SET HIBLCK14= @HIBLCK14,HICOND = @HICOND,HIATLEG = @HIATLEG,[HIATT#] = @HIATT,HIATNM = @HIATNM,HIOTHLEG = @HIOTHLEG,[HIOTH#] = @HIOTH,HIDOSF = @HIDOSF,HIDOST = @HIDOST,HIBLCK19 = @HIBLCK19,HIOCCD = @HIOCCD WHERE (HILOCX = @HILOCX) AND (HISEQ = @HISEQ) AND (HIDOS = @HIDOS);";

And then we will add in the values using an OrderedDictionary (does not need to be this way, could be any type of key& value object)
OrderedDictionary params = new OrderedDictionary();
params.add("@HIBLCK14", _billingDetails.Qual);
params.add("@HICOND", CondRelate);
params.add("@HIATLEG", billingDetails.AttendingLegacy);
params.add("@HIATT", _billingDetails.AttendingPhys);
params.add("@HIATNM", _billingDetails.AttendingPhysName);
params.add("@HIOTHLEG", _billingDetails.LOCATOR17A1);
params.add("@HIOTH", _billingDetails.LOCATOR17B);
params.add("@HIDOSF", _billingDetails.DOSFrom);
params.add("@HIDOST", ToDOS);
params.add("@HIBLCK19", _billingDetails.AdditionalClaimInformation);
params.add("@HIOCCD", _billingDetails.DateOfOccur);
params.add("@HILOCX", Locix);
params.add("@HISEQ", Sequence);
params.add("@HIDOS", DateOfService);
And then is the last bit, adding in a new method which accepts the collection of parameters and can add them to the command.
public bool ExecNonQuery(string Query, OrderedDictionary QueryValues) {
	try {
		using (SqlCommand cmd = new SqlCommand(Query, _conn)) {

			// iterate through the dictionary
			foreach (DictionaryEntry entry in QueryValues) {
				cmd.Parameters.AddWithValue(entry.Key, entry.Value);
			}

			if (_conn.State != ConnectionState.Open) { _conn.Open(); }
			cmd.ExecuteNonQuery();
			return true;
		}
	}
	catch (Exception) { throw; }
	finally { _conn.Close(); }
}


NOTES:
- While this is functional, it is not my preferred method as it limits the parameter functionality
- Some of your column names contained special characters. Those should be escaped.

I am not on an IDE equipped machine, so there may be syntax or other errors. The principal is sound and if it does not work there are no refunds.
   
Comments
dhiraj mane 10-Jan-19 6:08am
   
Thank you MadMyche... this solution will work for me.
MadMyche 11-Jan-19 7:30am
   
You're welcome
Rate this:
Please Sign up or sign in to vote.

Solution 3

Quote:
The query is throwing an error when a string parameter has a single quote(') value.

This is usually the starting point of an SQL injection.

Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100