ASP.NET

public int GetAccountSortByAccountCode(int account)
{
    using (var connection = new SqlConnection("YOUR CONNECTION STRING"))
    using (var command = new SqlCommand("SELECT ac_sort_order FROM lkup_account_codes where ac_code = @account", connection))
    {
        command.Parameters.AddWithValue("@account", account);

        connection.Open();
        return Convert.ToInt32(command.ExecuteScalar());
    }
}

public int GetAccountSortByAccountCode(int account)
        {
            QueryContainer Instance = new QueryContainer("SELECT ac_sort_order FROM lkup_account_codes where ac_code = " + account.ToString());

            return Convert.ToInt32(ExecuteScaler(Instance.Query));
        }  <pre>

And here is my new class:

<pre>
public class QueryContainer
    {
        string _query;

        public QueryContainer(string query) { _query = query; }

        public string Query
        {
            get
            {
                return _query;
            }

            set { _query = value;  }
        }
    }
<pre>

So what do you think. Does someone that I will still get SQL injection errors?

holdorf wrote:

QueryContainer Instance = new QueryContainer("SELECT ac_sort_order FROM lkup_account_codes where ac_code = " + account.ToString());

holdorf wrote:

My boss is upset and is giving me only a few days to fix this.

holdorf wrote:

Remember I have only one function to fix and handle this problem.

holdorf wrote:

Can someone please give me a small piece of code of how this should be implemented?

public class MyParam
    {
        public string name { get; set; }
        public string value { get; set; }
    }
    /// <summary>
    /// Summary description for QueryContainer SGH
    /// </summary>
    public class QueryContainer
    {

        string _query;

        public List<myparam> parameterList = new List<myparam>(); 

        public QueryContainer(string query) { _query = query; }

        public string Query
        {
            get
            {
                return _query;
            }

            set { _query = value;  }
        }
    }

The calling code:

<pre>
        public int GetAccountSortByAccountCode(int account)
        {
            QueryContainer Instance = new QueryContainer("SELECT ac_sort_order FROM lkup_account_codes where ac_code = <a href="http://www.codeproject.com/Members/account">@account</a>");

            MyParam myParam = new MyParam();

            myParam.name = "@account";
            myParam.value = account.ToString();

            Instance.parameterList.Add(myParam);

            return Convert.ToInt32(ExecuteScaler(Instance, 1));
        } 
<pre>

The implementing code:

<pre>
                if (_connection == null || _connection.State == ConnectionState.Closed)
                {
                    OpenConnection();
                }

                DbCommand command = _provider.CreateCommand();
                command.Connection = _connection;
                {
                    command.CommandText = Instance.Query;
                    command.CommandType = CommandType.Text;

                    foreach (var p in Instance.parameterList)
                    {
                        SqlParameter param = new SqlParameter(p.name, p.value);
                        command.Parameters.Add(param);
                    }

                    if (_useTransaction) { command.Transaction = _transaction; }

                    try
                    {
                        returnValue = command.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        if (ex is EntryPointNotFoundException)
                            throw ex;
                        //if (_useTransaction == true)
                        //_transaction.Rollback();
                        RollBack();

                        LogBLL bll = new LogBLL();
                        bll.WriteErrorLog(ex);

                        _iserror = true;
                    }

<pre>

        protected Object ExecuteScaler(QueryContainer Instance, int i)
        {

            object returnValue = null;


            if (!_iserror)
            {
                if (_trace)
                {
                    DoTrace("TAMIS.Data.Loader.ExecuteScalar", Instance.Query);
                }

                if (_connection == null || _connection.State == ConnectionState.Closed)
                {
                    OpenConnection();
                }

                DbCommand command = _provider.CreateCommand();
                command.Connection = _connection;
                {
                    command.CommandText = Instance.Query;
                    command.CommandType = CommandType.Text;

                    foreach (var p in Instance.parameterList)
                    {
                        SqlParameter param = new SqlParameter(p.name, p.value);
                        command.Parameters.Add(param);
                    }

                    if (_useTransaction) { command.Transaction = _transaction; }

                    try
                    {
                        returnValue = command.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        if (ex is EntryPointNotFoundException)
                            throw ex;
                        //if (_useTransaction == true)
                        //_transaction.Rollback();
                        RollBack();

                        LogBLL bll = new LogBLL();
                        bll.WriteErrorLog(ex);

                        _iserror = true;
                    }
<pre>

public int GetAccountSortByAccountCode(int account)
{

    int pValue = 0;

    string connStr = ConfigurationManager.ConnectionString("DefaultConnection").ConnectionString;
    SqlConnection conn = new SqlConnection(connStr);

    string query =
    "SELECT "  +
    " ac_sort_order "  +
    " FROM lkup_account_codes " +
    " WHERE ac_code = @account";

    SqlCommand  cmd = new SqlCommand(query, conn);

    SqlParameter param_account = new SqlParameter("@account", SqlDbType.Int);
    param_account .Value = account;
    cmd.Parameters.Add(param_account);

    try
    {
        conn.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            pValue = reader.GetInt32(0);
        }

        reader.Close();
    }
    catch (Exception ex)
    {

    }
    finally
    {
        conn.Close();
    }

    return pValue;
}