0

I have been using below code to execute my SQL Query, which looks something like this

SELECT abc.... FROM .... (many joins).. WHERE userid =" + userId + " AND UserState = " +userState ...; (Other parameters)

Below is how I am running the query and returning datatable

using (var context = new DbContext())
            {
                DataTable dt= new DataTable();
                var conn = context.Database.Connection;
                var connectionState = conn.State;
                try
                {
                    if (connectionState != ConnectionState.Open)
                        conn.Open();

                    using (var cmd = conn.CreateCommand())
                    {

                        cmd.CommandText = buildveryLongQuery(userId,userState);

                        cmd.CommandType = CommandType.Text;
                        using (var reader = cmd.ExecuteReader())
                        {
                            if (reader.HasRows)
                                dt.Load(reader);
                        }
                    }
                }

above method is working fine but it provides SQL injection. how can I parametrized it?

I tried below method:

Changed query to use @userId

IDbDataParameter personParam = cmd.CreateParameter();
personParam.DbType = DbType.Int32;
personParam.ParameterName = "@userId";
personParam.Value = userId;

but I am getting below error

Must declare the scalar variable \"@userId\

3
  • The parameter markers must be integrated into the query text returned from buildveryLongQuery(userId,userState);. Commented Sep 24, 2018 at 13:26
  • why are you using dbcontext solely to use its connection, and not use its SqlQuery<> function? Commented Sep 24, 2018 at 14:31
  • Which entity framework are you using? Most have a mechanism to create view objects which allow for binds / parameterized queries. Commented Sep 25, 2018 at 5:16

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.