Click here to Skip to main content
15,903,030 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
HI All,
I have a sql query where the column names are dynamic. How to avoid sql injection for this

C#
string.Format("SELECT {0},{1},{2} FROM Employee", a,b,c)

dynamic columns are more than 3.

What I have tried:

HI All,
I have a sql query where the column names are dynamic. How to avoid sql injection for this

C#
string.Format("SELECT {0},{1},{2} FROM Employee", a,b,c)

dynamic columns are more than 3.
Posted
Updated 26-Apr-16 21:17pm

One possible way is to get the schema for the table and compare the column names, no match no query.
 
Share this answer
 
Updated Solution.

C#
string[] yourDynamicColumns = { "Column1", "Column2", "Column3" };

            string queryFormat = "Select {0} From Employee";
            string dynamicQuery = "";
            SqlDataAdapter da = new SqlDataAdapter("select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Employee'", con);
            DataTable dtColumns = new DataTable ();
            da.Fill(dtColumns);
            foreach (DataRow row in dtColumns.Rows)
            {
                string columnName = row["COLUMN_NAME"].ToString();
                if (yourDynamicColumns.Contains(columnName))
                    dynamicQuery += columnName + ",";
            }
            dynamicQuery = dynamicQuery.TrimEnd(',');
            string query = string.Format(queryFormat, dynamicQuery);
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900