Solved

SQL Injection

Posted on 2013-06-25
8
235 Views
Last Modified: 2013-06-27
Can anyone help me to fix the sql injection for the following code

public bool ExecuteSql(string sql, string[] szArray)
 {
            using (SqlConnection oConn = new SqlConnection(cnEForm))
            {
                SqlCommand cmd = new SqlCommand(sql, oConn);

                if (null != szArray)
                {
                    int arrayLength = szArray.Length;

                    // Parameters must have names "@val1", "@val2", "@val3", etc...
                    for (int ii = 0; ii < arrayLength; ++ii)
                    {
                        string S = szArray[ii];

                        cmd.Parameters.Add("@val" + (ii + 1).ToString(), SqlDbType.VarChar,  
                             S.Length).Value = S;
                    }
                }


                cmd.ExecuteNonQuery();  <-- sql injection right here (CWE ID 89)

                return true;
            }
        }
0
Comment
Question by:tommychiu
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 250 total points
ID: 39276901
Actually it is telling you that you have not done anything above that to eliminate SQL commands that could cause problems from the data.  More info: http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx

You should probably click on "Request Attention" and get the SQL Server zone added to your question.
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39276904
Whats the problem?
0
 

Author Comment

by:tommychiu
ID: 39279677
I changed the code a little bit:

        public System.Data.DataSet GetDataSet(string sql, string[] szArray)
        {
            using (SqlConnection oConn = new SqlConnection(cnEForm))
            {
                //SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, oConn);
                SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT * FROM Users  
                     WHERE Firm = @val1 AND LoginID = @val2", oConn);
                DataSet ds = new DataSet();

                // Parameterization of the query string
                if (null != szArray)
                {
                    int arrayLength = szArray.Length;

                   for (int ii = 0; ii < arrayLength; ++ii)
                    {
                        string S = szArray[ii];

                        dataAdapter.SelectCommand.Parameters.Add("@val" + (ii + 1).ToString(),
                           SqlDbType.VarChar, S.Length).Value = S;
                    }
                }

                dataAdapter.Fill(ds);

                return ds;
            }
        }

Now, if I put the "SELECT statement" inside the dataAdapter then the sql injection fixed. Unfortunately, I cannot hard code the "SQL statement". Do you all have any idea?
0
 
LVL 40
ID: 39280174
Make sure that S.Length is a reasonable value.

The way you are doing it, you are setting the length of the parameter to the data that is passed. Anything can go in.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:tommychiu
ID: 39280306
Here is the updated code:

        public System.Data.DataSet GetDataSet(string sql, string[] szArray)
        {
            using (SqlConnection oConn = new SqlConnection(cnEForm))
            {
                //SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, oConn);
                SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT * FROM Users  
                     WHERE Firm = @val1 AND LoginID = @val2", oConn);
                DataSet ds = new DataSet();

                // Parameterization of the query string
                if (null != szArray)
                {
                    int arrayLength = szArray.Length;

                   for (int ii = 0; ii < arrayLength; ++ii)
                    {
                       dataAdapter.SelectCommand.Parameters.Add("@val" + (ii + 1).ToString(),
                           SqlDbType.VarChar).Value =  szArray[ii];
                    }
                }

                dataAdapter.Fill(ds);

                return ds;
            }
        }

Now, if I put the "SELECT statement" inside the dataAdapter then the sql injection fixed. Unfortunately, I cannot hard code the "SQL statement". Do you all have any idea?
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39280354
What is telling you that?  It may be because it can't examine the SQL statements when they are not hard coded.
0
 
LVL 40
ID: 39280500
The idea is not to remove the Length. You are not solving SQL injection problems by doing so. It is to make sure that it is not too big.

As an example, if you do not expect any piece Firm to be over 40 characters and the LoginID to be over 10 characters , then check the lengths before setting them in the parameter definitions. If any of them gets bigger than the limit you have set, then either stop the application with a SecurityException or truncate it to number of characters you decided upon.
0
 
LVL 11

Accepted Solution

by:
b_levitt earned 250 total points
ID: 39281224
I'm not really sure why people still do these wrapper functions that end up hiding nearly all of the functionality that ado.net has to offer.

That said, there's nothing about this particular block of code that's susceptible to injection.  The injection point is more likely to be where the "string sql" parameter is being constructed outside of the method.  Please paste the code that calls this method and more importantly the code creates the "sql" string.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to read XML file attributes... 17 42
Expando 4 35
Set form below another form 3 26
Trouble connecting to SqlServer database 4 33
Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now