none
ADO.NET SQL insert special chars RRS feed

  • Question

  • Hi Guys,

    I have my library for sqlserver insertions used by other developers. I have a common method to isert, update and delete as below:

    Query will be passed as an input to this method.

    I am not sure how to handle the special characters coming in in the insert and update queries. It keeps failing. How to change the code to handle it when the qery is passed.

    And is it standard to allow the complete query to be passed into the metod like that.

    Please suggest. .

     public int InsertUpdateDeleteData(string queryString)
            {
                int recordsAffected = -1;

                try
                {
                    using (SqlConnection connection = new SqlConnection(mConString))
                    {
                        mSqlCommand = new SqlCommand(queryString, connection);
                        mSqlCommand.Connection.Open();
                        recordsAffected = mSqlCommand.ExecuteNonQuery();
                    }
                    return recordsAffected;
                }
                catch (Exception)
                {               
                    throw;

                }
                finally
                {
                    CleanUp();
                }
            }

    Thanks.

    Friday, April 12, 2019 3:35 PM

Answers

  • Hello,

    Can you indicate what special characters are causing issues?

    In regards to passing the SQL statement, that is up to you but would advise to construct the SQL statements in the method while passing in any parameters needed e.g. values for an insert or update, primary key for removing etc.

    I would separate insert from update as having the new primary is important 99 percent of the time. Usually when trying to be efficient by using one method for multiple operations tends to constrain things.

    Although many throw an exception I never do this, always have a mechanism for returning to the caller and be able to integrate if there was an exception and what was the exception w/o a throw.  


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Aryaa Tuesday, April 16, 2019 1:19 PM
    Friday, April 12, 2019 4:13 PM
    Moderator
  • Personally, your method doesn't do anything that is useful so I wouldn't go that route. Furthermore your method isn't flexible or safe. Here's the issues I have with your code.

    1) InsertUpdateDeleteData is the method name but I can pass any query I want. The method name doesn't actually related to what I can do. What if I want to create a sproc? I can do that with your method so it isn't really well named.

    2) The return value isn't useful in most cases. Returning the # of records affected is only useful if NOCOUNT is on (which we generally turn off) and/or the # of rows returned actually has meaning. Perhaps for update/delete it could be useful but for insert I would rather know the primary key of the row I just inserted otherwise how can I query for it later.

    3) There is no way to pass parameters to your query. Therefore the query has to use string replacement which is a primary way of SQL injection attacks. Thus your data layer is encouraging a pattern that is not recommended under any circumstances.

    4) The try-catch-finally doesn't seem to be doing anything. Having a catch that just throws is always the wrong thing to do. Just leave it off. But what does this CleanUp method do? There isn't anything in your method that seems to need clean up so is it cleaning up the instance? If so then what happens if I want to call this method twice in a row?

    My personal recommendation is to use a generic data layer that wraps the entire command rather than a simple query string. Then you can either wrap the connection as well or use extension methods if you want to hide the whole connection/command code. Personally I wrote my own library for doing that but there are some third party libraries you can use as well.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Aryaa Tuesday, April 16, 2019 1:19 PM
    Friday, April 12, 2019 6:36 PM
    Moderator
  • Hi

    Thank you for posting here.

    Based on your description, you want to insert special chars in ADO.NET.

    I used the following code, it could insert special chars in ADO.NET.

    class Program
        {
            static void Main(string[] args)
            {
                string querystring = "insert into Student(Id,Name) values('4','*&$#')";
                int m = InsertUpdateDeleteData(querystring);
                Console.WriteLine(m);
                Console.ReadKey();
            }
            public static int InsertUpdateDeleteData(string queryString)
            {
                int recordsAffected = -1;
                string mConString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Test;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
                SqlCommand mSqlCommand;
                try
                {
                    using (SqlConnection connection = new SqlConnection(mConString))
                    {
                        mSqlCommand = new SqlCommand(queryString, connection);
                        mSqlCommand.Connection.Open();
                        recordsAffected = mSqlCommand.ExecuteNonQuery();
                    }
                    return recordsAffected;
                }
                catch (Exception)
                {
                    throw;
    
                }
                finally
                {
                   
                }
            }
    }
    
    Database:

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Aryaa Tuesday, April 16, 2019 2:40 PM
    Monday, April 15, 2019 5:44 AM
    Moderator

All replies

  • Hello,

    Can you indicate what special characters are causing issues?

    In regards to passing the SQL statement, that is up to you but would advise to construct the SQL statements in the method while passing in any parameters needed e.g. values for an insert or update, primary key for removing etc.

    I would separate insert from update as having the new primary is important 99 percent of the time. Usually when trying to be efficient by using one method for multiple operations tends to constrain things.

    Although many throw an exception I never do this, always have a mechanism for returning to the caller and be able to integrate if there was an exception and what was the exception w/o a throw.  


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Aryaa Tuesday, April 16, 2019 1:19 PM
    Friday, April 12, 2019 4:13 PM
    Moderator
  • Personally, your method doesn't do anything that is useful so I wouldn't go that route. Furthermore your method isn't flexible or safe. Here's the issues I have with your code.

    1) InsertUpdateDeleteData is the method name but I can pass any query I want. The method name doesn't actually related to what I can do. What if I want to create a sproc? I can do that with your method so it isn't really well named.

    2) The return value isn't useful in most cases. Returning the # of records affected is only useful if NOCOUNT is on (which we generally turn off) and/or the # of rows returned actually has meaning. Perhaps for update/delete it could be useful but for insert I would rather know the primary key of the row I just inserted otherwise how can I query for it later.

    3) There is no way to pass parameters to your query. Therefore the query has to use string replacement which is a primary way of SQL injection attacks. Thus your data layer is encouraging a pattern that is not recommended under any circumstances.

    4) The try-catch-finally doesn't seem to be doing anything. Having a catch that just throws is always the wrong thing to do. Just leave it off. But what does this CleanUp method do? There isn't anything in your method that seems to need clean up so is it cleaning up the instance? If so then what happens if I want to call this method twice in a row?

    My personal recommendation is to use a generic data layer that wraps the entire command rather than a simple query string. Then you can either wrap the connection as well or use extension methods if you want to hide the whole connection/command code. Personally I wrote my own library for doing that but there are some third party libraries you can use as well.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Aryaa Tuesday, April 16, 2019 1:19 PM
    Friday, April 12, 2019 6:36 PM
    Moderator
  • Hi

    Thank you for posting here.

    Based on your description, you want to insert special chars in ADO.NET.

    I used the following code, it could insert special chars in ADO.NET.

    class Program
        {
            static void Main(string[] args)
            {
                string querystring = "insert into Student(Id,Name) values('4','*&$#')";
                int m = InsertUpdateDeleteData(querystring);
                Console.WriteLine(m);
                Console.ReadKey();
            }
            public static int InsertUpdateDeleteData(string queryString)
            {
                int recordsAffected = -1;
                string mConString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Test;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
                SqlCommand mSqlCommand;
                try
                {
                    using (SqlConnection connection = new SqlConnection(mConString))
                    {
                        mSqlCommand = new SqlCommand(queryString, connection);
                        mSqlCommand.Connection.Open();
                        recordsAffected = mSqlCommand.ExecuteNonQuery();
                    }
                    return recordsAffected;
                }
                catch (Exception)
                {
                    throw;
    
                }
                finally
                {
                   
                }
            }
    }
    
    Database:

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Aryaa Tuesday, April 16, 2019 2:40 PM
    Monday, April 15, 2019 5:44 AM
    Moderator
  • Hi Thank you very much for your guidance.

    I think having separate methods are the right way to do so....I agree with you. Thank you for educating me.

    Below is the sample code for Inserting.... will this be a good start or even this is not good code to move fwd:

            public long InsertData(string tableName, Dictionary<string, string> insertKeyValPairs)
            {
                long primaryKeyNumber;
                StringBuilder sqlParams = new StringBuilder();
                StringBuilder sql = new StringBuilder();
                sql.Append("INSERT INTO dbo.");
                sql.Append(tableName);
                sql.Append("(");

                foreach (var item in insertKeyValPairs)
                {
                    sql.Append((string.IsNullOrEmpty(sql.ToString()) == false) ? "," : "");
                    sql.Append(item.Key);

                    sqlParams.Append((string.IsNullOrEmpty(sqlParams.ToString()) == true) ? "@" : ",@");
                    sqlParams.Append(item.Key);
                }
                sql.Append(") VALUES(");
                sql.Append(sqlParams.ToString());
                sql.Append(")");

                using (SqlConnection cn = new SqlConnection(mConString))
                {
                    using (SqlCommand cmd = new SqlCommand(sql.ToString(), cn))
                    {  // define parameters and their values
                        foreach (var item in insertKeyValPairs)
                        {
                            cmd.Parameters.Add(item.Value);
                        }
                        // open connection, execute INSERT, close connection
                        cn.Open();
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "SELECT SCOPE_IDENTITY() AS 'Identity'";
                        DataTable dtData = new DataTable();
                        dtData.Load(mSqlCommand.ExecuteReader());
                        primaryKeyNumber = Convert.ToInt64(dtData.Rows[0][0]);

                        cn.Close();
                    }
                }
                return primaryKeyNumber;
            }

    Still working on Update and delete part..... thought better to get some corrections on insert before proceeding further.

    Thank you in Advance.

    Monday, April 15, 2019 7:19 PM
  • Hi Thank you very much for your guidance.

    I think having separate methods are the right way to do so....I agree with you. Thank you for educating me.

    Below is the sample code for Inserting.... will this be a good start or even this is not good code to move fwd:

            public long InsertData(string tableName, Dictionary<string, string> insertKeyValPairs)
            {
                long primaryKeyNumber;
                StringBuilder sqlParams = new StringBuilder();
                StringBuilder sql = new StringBuilder();
                sql.Append("INSERT INTO dbo.");
                sql.Append(tableName);
                sql.Append("(");

                foreach (var item in insertKeyValPairs)
                {
                    sql.Append((string.IsNullOrEmpty(sql.ToString()) == false) ? "," : "");
                    sql.Append(item.Key);

                    sqlParams.Append((string.IsNullOrEmpty(sqlParams.ToString()) == true) ? "@" : ",@");
                    sqlParams.Append(item.Key);
                }
                sql.Append(") VALUES(");
                sql.Append(sqlParams.ToString());
                sql.Append(")");

                using (SqlConnection cn = new SqlConnection(mConString))
                {
                    using (SqlCommand cmd = new SqlCommand(sql.ToString(), cn))
                    {  // define parameters and their values
                        foreach (var item in insertKeyValPairs)
                        {
                            cmd.Parameters.Add(item.Value);
                        }
                        // open connection, execute INSERT, close connection
                        cn.Open();
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "SELECT SCOPE_IDENTITY() AS 'Identity'";
                        DataTable dtData = new DataTable();
                        dtData.Load(mSqlCommand.ExecuteReader());
                        primaryKeyNumber = Convert.ToInt64(dtData.Rows[0][0]);

                        cn.Close();
                    }
                }
                return primaryKeyNumber;
            }

    Still working on Update and delete part..... thought better to get some corrections on insert before proceeding further.

    Thank you in Advance.

    Monday, April 15, 2019 7:19 PM
  • I think it doesn't insert '

    anyway thank you for the support. I decided to write separate methods for each operation.

    Monday, April 15, 2019 7:21 PM
  • Hi

    Is your problem solved? If so, please post "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue. If not, please feel free to let me know.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 16, 2019 2:14 AM
    Moderator