We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Why dataset make the connectionpool reach max pool size? Being Hijacked?

Medium Priority
372 Views
Last Modified: 2013-12-17
Hi

I use asp.net, vc# and mssql2005 server to build web application. There are several databind with dataset or SqlDataReader.

I set the connection string with max pool size=300;

However, when the website goes public, I suspect someone SQL hijack as sometimes I received exception said varchar value '11 and char(124)+user+char(124)=0 to data type int.

Even ds.dispose still no use. It will make connection timeout and fill out the connection pool.

How should I fix it?


private void Bind_Item(string p_ID)
        {
            string strSql = "select  * FROM product where category = '" + p_ID + "' order by id desc";
            DataSet ds = new DataSet();
            try
            {
                ds = SqlHelper.ExecuteDataset(DBConnection.ConnString, CommandType.Text, strSql);
                CollectionPager3.DataSource = ds.Tables[0].DefaultView;
                CollectionPager3.BindToControl = ItemUserControl.PublicRepeaterInUC;
                ItemUserControl.PublicRepeaterInUC.DataSource = CollectionPager3.DataSourcePaged;
                ds.Dispose();
                ds = null;
            }
            catch (Exception e)
            {
                ds.Dispose();
                ds = null;               
                Response.Redirect("error.html", true);
            }
            finally
            {
            }            
        }

Open in new window

Comment
Watch Question

Disposing of the dataset has no effect on the connection. You should check the SqlHelper.ExecuteDataset  method, make sure that closes and disposes of the connection it uses to retrieve the dataset.

Also, yes you are opening yourself up to SQL injection by generating the SQL query like this.
The ideal solution would be to create a stored procedure that accepts an @Id parameter, which then does:
SELECT * FROM Product WHERE category = @Id

As a side note, I'd recommend moving your "ds.Dispose();" into the finally block - it will always get called if there's an exception or not and means you don't need to duplicate the clean code in multiple places.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.