2

When user logs in. I Open connection 'conn' which is a public static member my dbclass which has all functions related to database. Then conn remains open until application is closed

It does not degrade the application performance measurably but enhances the database queries performance remarkably.

Is there any security issue? If the database is centralized and two or 3 PC's using it with independent applications, Then what security issue can be there due to opening connection on log in?

Or any other problem to be faced in future?

Edit in reply to first comment

I checked by timespan.totalmilliseconds. It takes bit lesser than twice time when involves open and executing query than just executing query when connection is already open

Edit --- The Actual answer required.

If some one with good reason tells me that no there is no security issue or with a good reason tells yes this one is the major security issue. That will be accepted answer after waiting others responses.

By Google search i did not find any article/answer which would be to the point to my question.

Sami
  • 8,168
  • 9
  • 66
  • 99
  • 3
    "It does not degrade the application performance measurably but enhances the database queries performance remarkably." - please explain how you measured that.... Best practice is to hold a connection open only for as long as is necessary. Let connection pooling work for you... – Mitch Wheat Aug 24 '12 at 11:18
  • I checked by timespan.totalmilliseconds. It takes bit lesser than twice time when involves open and executing query than just executing query when connection is already open – Sami Aug 24 '12 at 11:21
  • That seems rather unlikely.... – Mitch Wheat Aug 24 '12 at 11:22
  • Actually the purpose of a connection pool is to have the connections open all the time, at least for a minimum numbr of connections that is needed, because opening a connection introduces considerable latency. – Jens Schauder Aug 24 '12 at 11:23
  • It surely depends upon my hardware performance as well. But you can check it on your own hardware. By executing simple queries on single table. – Sami Aug 24 '12 at 11:25
  • @JensSchauder. Thanks for telling about connection pool. I have not studied it. I will do it today. – Sami Aug 24 '12 at 11:29
  • 2
    Your stuck if your application ever needs to become multi-threaded - unless you start implementing locks all over the place, whenever the connection is required. This quickly gets messy and slows things down - breaking the whole reason you tend to introduce multi-threading in the first place. Contrast this with the Create/Open/Execute/Close pattern, which will continue to work with no adjustments. – Damien_The_Unbeliever Aug 24 '12 at 12:17
  • Yes That is a performance issue. Write it in answer. I wanna vote up. – Sami Aug 24 '12 at 12:24
  • It's not a security issue though, which is what you were asking for. – Damien_The_Unbeliever Aug 24 '12 at 14:03

2 Answers2

2

The first issue I can think of is the case where for whatever reason the connection closes (firewall ?).

If you are keeping the connection open and for some reason the connection is forced to close, your program will need to be restarted in order to be usable again. But, if you only open the connection when a query needs to be made then close it again. Even if the connection is closed then your program will try to reconnect when a new query needs to be made.

This is assuming we are talking about a single computer accessing a single database. If we are talking about a database that more people can access then never closing the connection will cause issues with the host (maximum connections reached) at some point. Not to mention how prone it will be to attacks that cause your connection numbers to reach maximum in just a few seconds.

denied66
  • 644
  • 7
  • 18
  • Good points. In my case both are avoidable issues. I have a catch as a precaution of this issue which tries to open connection once more when it is not already open and tries same query if again fails then throws exception. PC's will be off after work time and log off contains connection closing. But you have highlited valid issues – Sami Aug 24 '12 at 11:50
  • Still trying to figure out what kind of security risks are you looking for. It looks like the database connection string is within the application, which makes the credentials accessible to a user with decent knowledge. So, since your credentials are already exposed and the users already can access the database with other means if they choose to, what are you worrying about exactly ? Except from the things I mentioned above, the other issue you will face later on is the system scaling. Assuming users keep increasing or you want to use what you are creating now on another project. – denied66 Aug 24 '12 at 14:49
0

The risk is that connection string may be stolen. Even if you declare your SqlConnection object as private. You should minimize user privileges. Also it's possible to encrypt connection string. But in any case I think it's unsafe.

seeker
  • 3,255
  • 7
  • 36
  • 68
  • 1
    This risk exists no matter if you open a connection once and keep it open or open and close it over and over again. – Jens Schauder Aug 24 '12 at 11:27
  • How scan actually opening connection long time be the reason of connection string stealing? Can it be stolen only when connection is open? – Sami Aug 24 '12 at 11:33
  • According to Jens Schauder risk is same. And you also did not say that opening it once causes of steeling. Then What can be the negative issues with opening the database connection once – Sami Aug 24 '12 at 11:40