none
Use parameter in sqlcommand

    Question

  • Hello,

    I read this lesson in this link. The writer recommended us use parameter instead  direct variable in sql command for security.

    But when read from textbox directly or indirectly use parameter finally we read from textbox. what difference? 

    What means "Anything placed into a parameter will be treated as field data, not part of the SQL statement, which makes your application much more secure."

    Thanks



    • Edited by Arash_89 Tuesday, February 06, 2018 1:49 PM
    Tuesday, February 06, 2018 1:47 PM

Answers

  • Hi Arash_89,

    From this article, we can see that

    Command objects use parameters to pass values to SQL statements or stored procedures, providing type checking and validation. Unlike command text, parameter input is treated as a literal value, not as executable code. This helps guard against "SQL injection" attacks, in which an attacker inserts a command that compromises security on the server into an SQL statement.

    Parameterized commands can also improve query execution performance, because they help the database server accurately match the incoming command with a proper cached query plan.

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/configuring-parameters-and-parameter-data-types

    Best Regards,

    Cherry


    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.

    Wednesday, February 07, 2018 5:51 AM

All replies

  • Here is the deal, there is a lot said about security that it’s bad to not use parameters yet for most apps out there I have not heard of this being an issue.

    Now in favor of parameters, sure it does better secure the app but even more important is that when passing values to a query be it SELECT, UPDATE, DELETE or ADD parameters properly format your data passed to the query. A classic example, you pass something with apostrophes e.g. My Cake’s, this causes a malformed statement while using a parameter it will give us My Cake’’s for you which is properly formatted etc.

    See my code sample for more on this, properly writing queries. 

    https://code.msdn.microsoft.com/Writing-SQL-Statements-00fe697f

    A downside of using parameters is not being able to see what the real query looks like when debugging but you can use the code below to see it.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace PeekCommand
    {
        /// <summary>
        /// Contains language extension methods for assisting with parameterized command statements
        /// </summary>
        public static class IDbExtensions
        {
            /// <summary>
            /// Used to show an SQL statement with actual values
            /// </summary>
            /// <param name="pCommand">Command object</param>
            /// <returns>Command object command text with parameter values</returns>
            public static string ActualCommandText(this IDbCommand pCommand)
            {
                StringBuilder sb = new StringBuilder(pCommand.CommandText);
                IDataParameter EmptyParameterNames = (from T in pCommand.Parameters.Cast<IDataParameter>() where string.IsNullOrWhiteSpace(T.ParameterName) select T).FirstOrDefault();
    
                if (EmptyParameterNames != null)
                {
                    return pCommand.CommandText;
                }
    
                foreach (IDataParameter p in pCommand.Parameters)
                {
                    if ((p.DbType == DbType.AnsiString) || (p.DbType == DbType.AnsiStringFixedLength) || (p.DbType == DbType.Date) || (p.DbType == DbType.DateTime) || (p.DbType == DbType.DateTime2) || (p.DbType == DbType.Guid) || (p.DbType == DbType.String) || (p.DbType == DbType.StringFixedLength) || (p.DbType == DbType.Time) || (p.DbType == DbType.Xml))
                    {
                        if (p.ParameterName.Substring(0, 1) == "@")
                        {
                            if (p.Value == null)
                            {
                                throw new Exception("no value given for parameter '" + p.ParameterName + "'");
                            }
                            sb = sb.Replace(p.ParameterName, string.Format("'{0}'", p.Value.ToString().Replace("'", "''")));
                        }
                        else
                        {
                            sb = sb.Replace(string.Concat("@", p.ParameterName), string.Format("'{0}'", p.Value.ToString().Replace("'", "''")));
                        }
                    }
                    else
                    {
                        sb = sb.Replace(p.ParameterName, p.Value.ToString());
                    }
                }
    
                return sb.ToString();
            }
        }
    
    }
    

    So we can do (where cmd is the command object)

    Console.WriteLine(cmd.ActualCommand())

    To see the entire query with parameter values as they would be seen by the database.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, February 06, 2018 2:14 PM
  • Hi Arash_89,

    From this article, we can see that

    Command objects use parameters to pass values to SQL statements or stored procedures, providing type checking and validation. Unlike command text, parameter input is treated as a literal value, not as executable code. This helps guard against "SQL injection" attacks, in which an attacker inserts a command that compromises security on the server into an SQL statement.

    Parameterized commands can also improve query execution performance, because they help the database server accurately match the incoming command with a proper cached query plan.

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/configuring-parameters-and-parameter-data-types

    Best Regards,

    Cherry


    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.

    Wednesday, February 07, 2018 5:51 AM
  • Here is the deal, there is a lot said about security that it’s bad to not use parameters yet for most apps out there I have not heard of this being an issue.

    Now in favor of parameters, sure it does better secure the app but even more important is that when passing values to a query be it SELECT, UPDATE, DELETE or ADD parameters properly format your data passed to the query. A classic example, you pass something with apostrophes e.g. My Cake’s, this causes a malformed statement while using a parameter it will give us My Cake’’s for you which is properly formatted etc.

    See my code sample for more on this, properly writing queries. 

    https://code.msdn.microsoft.com/Writing-SQL-Statements-00fe697f

    A downside of using parameters is not being able to see what the real query looks like when debugging but you can use the code below to see it.

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace PeekCommand
    {
        /// <summary>
        /// Contains language extension methods for assisting with parameterized command statements
        /// </summary>
        public static class IDbExtensions
        {
            /// <summary>
            /// Used to show an SQL statement with actual values
            /// </summary>
            /// <param name="pCommand">Command object</param>
            /// <returns>Command object command text with parameter values</returns>
            public static string ActualCommandText(this IDbCommand pCommand)
            {
                StringBuilder sb = new StringBuilder(pCommand.CommandText);
                IDataParameter EmptyParameterNames = (from T in pCommand.Parameters.Cast<IDataParameter>() where string.IsNullOrWhiteSpace(T.ParameterName) select T).FirstOrDefault();
    
                if (EmptyParameterNames != null)
                {
                    return pCommand.CommandText;
                }
    
                foreach (IDataParameter p in pCommand.Parameters)
                {
                    if ((p.DbType == DbType.AnsiString) || (p.DbType == DbType.AnsiStringFixedLength) || (p.DbType == DbType.Date) || (p.DbType == DbType.DateTime) || (p.DbType == DbType.DateTime2) || (p.DbType == DbType.Guid) || (p.DbType == DbType.String) || (p.DbType == DbType.StringFixedLength) || (p.DbType == DbType.Time) || (p.DbType == DbType.Xml))
                    {
                        if (p.ParameterName.Substring(0, 1) == "@")
                        {
                            if (p.Value == null)
                            {
                                throw new Exception("no value given for parameter '" + p.ParameterName + "'");
                            }
                            sb = sb.Replace(p.ParameterName, string.Format("'{0}'", p.Value.ToString().Replace("'", "''")));
                        }
                        else
                        {
                            sb = sb.Replace(string.Concat("@", p.ParameterName), string.Format("'{0}'", p.Value.ToString().Replace("'", "''")));
                        }
                    }
                    else
                    {
                        sb = sb.Replace(p.ParameterName, p.Value.ToString());
                    }
                }
    
                return sb.ToString();
            }
        }
    
    }

    So we can do (where cmd is the command object)

    Console.WriteLine(cmd.ActualCommand())

    To see the entire query with parameter values as they would be seen by the database.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thank you but below link not published.

    https://code.msdn.microsoft.com/Writing-SQL-Statements-00fe697f

    Friday, February 09, 2018 4:06 PM

  • VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thank you but below link not published.

    https://code.msdn.microsoft.com/Writing-SQL-Statements-00fe697f

    I just checked and you are correct so I just published the code sample.

    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, February 11, 2018 4:19 PM
  • And the exploits of "Little Bobby Tables" should remind us of the bad things that can happen with SQL Injection:

    https://xkcd.com/327/

    ;0)


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, February 11, 2018 5:16 PM