Solved

Are SQL Injections a concern for desktop applications?

Posted on 2013-11-20
9
524 Views
Last Modified: 2013-11-25
I have a general question.  We are migrating a Manufacturing Plant MIS system from DBF to SQL 2008 R2.

We are ready to release our initial version to a few beta customers.

But I have read alot about SQL injection attacks and how you should never code queries into your program but pass parameters to stored procedures.

Is this a concern mostly for web applications?  Or is it also a concern with desktop applications written in VB6 and VB2012?  

Thanks!
0
Comment
Question by:lthames
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 9

Expert Comment

by:Beartlaoi
ID: 39664099
Mostly Web apps - yes, however it remains possible that a valid non-administrator user of the application may try to use this to give themselves stuff.  
It is also good habit to practice safe coding at all times so that you do not forget to do it when coding apps for risky environments.
And just using stored procedure parameters is not always enough depending on what the stored procedure is going to do with the parameters (like make an ad-hoc query and exec it)
Be sure to use functions that encode or strip special characters.
0
 
LVL 9

Expert Comment

by:Beartlaoi
ID: 39664107
Also since code gets copied between projects, its better if all code is hardened.
0
 
LVL 7

Expert Comment

by:Ironhoofs
ID: 39664120
Its allways a concern, but the risk may vary with the setup of the application. if you are hosting the database and it contains proprietary information (yours or other customers), the risk is different from a situation where the customer hosts the database on site and only employees can access it. In the end, i would allways go for the save option. It will not only minimize the risk of information leakage but can prevent malicious code from damaging your database as well.
0
 

Author Comment

by:lthames
ID: 39664217
In our case, the database is installed on site on a local area network and there is no remote access to the applications.

There is a web module but it is written separately and already uses only stored procedures to communicate with the database.

The plan for phase 2 is to migrate all of the queries from inline to stored procedures . . . but at the time I hadn't thought about any security risks.

Also, is the security risk only in INSERT and UPDATE commands or also in SELECT queries?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 7

Accepted Solution

by:
Ironhoofs earned 200 total points
ID: 39664272
Any executable query that uses unfiltered userdata is at risk. Suppose you have code like
string SQLQuery = "SELECT * FROM customers WHERE cust_id = '" + UserInput + "'"

Open in new window


If the user inputs "'; DELETE * FROM sometable;REM", the final query would read
string SQLQuery = "SELECT * FROM customers WHERE cust_id = ''; DELETE * FROM sometable;REM'"

Open in new window


All sorts of things could be executed: selecting data, inserting records to circumvent security, damage the database, etc. Because there are so many options you may not think about, filtering the userdata is deemed less secure.
0
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 200 total points
ID: 39664612
SQL injection can come from anywhere, web, windows application, from outside or internally on the network.

There are surely tables that should not be accessed by all the users, so a disgruntled employee could use it to break things in the database.

Injection can be used in a SELECT to query the system tables in order to know the structure of the database. Once this is known, it could be used again to query anything.

Parametized stored procedures where the size of the text fields has been set to proper values are the best way to protect against SQL Injection.

If not possible, making sure to limit the number of characters that the user can enter in any kind of input control is another.

If you use custom classes to communicate with the database, you get a third place where you could check to make sure that strings passed to the database are not longer than necessary.

I know companies where they insist that any communication with the database must be done through a service, adding another level of protection.

Ideally, you combine all these techniques. So if there is something you forget somewhere, you will end up trapping the bad guy on the next level of protection.
0
 
LVL 11

Expert Comment

by:SAMIR BHOGAYTA
ID: 39665062
There is a big difference between a web environment and a desktop environment. When developing web sites and services, the thing you don't trust is the user (user input). When running a desktop application, the thing that isn't trusted is the application itself, or atleast, a system administrator would like to know whether the application itself doesn't do any harm, since code the runs on the local computer is a risk by itself.

So in a sense, for you as a developer of a desktop application, security rules not always apply, since the application you run is not a black box, but a white box. With a web service / site, you expect attacks to not be able to change the internal state, but with any desktop app (Java, .NET, native) it is 'quite' easy to change the state of the application while the application is running and especially with Java and .NET, debugging and decompiling an application is quite easy.

In other words, you must consider the desktop application completely compromised, and if this is a risk, you must extract everything that must be secure (authentication, authorization, validation) to an external (web) service. For this service, the 'normal' OWASP rules apply.

Things you should watch, is that it's really hard to completely secure your data layer, when a desktop application connects directly to a database. For instance, SQL injection is not an issue for your desktop application in this case, since when the application can directly connect to the database, so can the user. And if the user can connect to the database, he can execute any arbitrary query. This is an extreme form of SQL injection, but it completely skips your application.

Trying to secure a 2 tier application, often means the use of stored procedures as intermediate (service) layer (and preventing direct access to tables). Developing and maintaining stored procedures is much more costly than developing a .NET (web) service.
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 100 total points
ID: 39673650
Always remember to sanitize your inputs.
0
 

Author Closing Comment

by:lthames
ID: 39674893
Thanks. . . . This was all quite a lesson that I wasn't aware of.

We already check the user inputs.  MOST user inputs are qualified to the specific need (single valid dates, input within the right numerical range, etc).  But this makes me wonder about some a couple of functions (job search and customer search) that are not already qualified and could be an issue.

I will start with moving those to stored procedures that protect from entry.

Thanks again
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

705 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

13 Experts available now in Live!

Get 1:1 Help Now