Solved

SQL DB Connection (safest way)

Posted on 2011-02-11
7
433 Views
Last Modified: 2012-05-11
Hello,

I am currently creating a site in ASP. The site uses a backend SQL database to deal with requsets to and from the ASP pages.

I have previously used the following code to connect to my SQL databases. My question is, is this a recommended way to connect to a SQL database with ASP? Is it vulnerable from anything such as a code injection attack?

Is there another way which is recommended which could make the query of the database better?


set objConnection=Server.CreateObject("ADODB.Connection")
     		 
		 objConnection.Open "Provider=sqloledb;" & _ 
           "Data Source=IPaddress;" & _
           "Initial Catalog=username;" & _
           "User Id=username;" & _
           "Password=password" 

Example Query :

	strSQL = "SELECT * FROM table Where emanus = '" & us_e_un & "'" 
	SET chk_rslt = objConnection.Execute (strSQL)

Open in new window

0
Comment
Question by:gisvpn
7 Comments
 
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 125 total points
Comment Utility
0
 
LVL 7

Assisted Solution

by:Cboudroz
Cboudroz earned 175 total points
Comment Utility
SQL injection will be possible depending of what you found in the variable us_e_un

Ex:

if a user enter in the variable us_e_un = "HELLO'' GO DROP TABLE EMPLOYE"

the execute command will send to SQL

SELECT * FROM table Where emanus = 'HELLO' go DROP TABLE employe

You will drop your table employe from database.


So you should always avoid to string your query in .net.  Best way is to create store procedure on SQL server and call procedure from .net



CREATE PROC table_LST
@emanus  varchar(100) -- or int, or bit ...
as 

SELECT * FROM table Where emanus = @emanus  

go

-- in .net

ArrayList parameters = new ArrayList();

SqlParameter param = new SqlParameter("@emanus", SqlDbType.Int);
param.Direction = ParameterDirection.Input;
param.Value = us_e_un;
parameters.Add(param);


SqlParameter retVal = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
retVal.Direction = ParameterDirection.ReturnValue;
parameters.Add(retVal);

CsEmanus  ds = new CsEmanus();

SqlClient.ExecStoredProc("table_LST", parameters, ds);
return ds;

Open in new window


Whit Store procedure you will send to SQL @emanus = 'HELLO'' GO DROP TABLE EMPLOYE'

and the procedure will try the :

select * from table where emanus = 'HELLO'' GO DROP TABLE EMPLOYE'


so you have no risk of dropping table or other injection.

hope that can help.

0
 

Author Comment

by:gisvpn
Comment Utility
Hi,

cs97jjm3 - Thansk for the post, I think however the link shows as a disadvantgae (code injection) the problem I am trying to work out from this post.

Cboudroz - Thanks also for the post, would you be able to explain a little more. I have not used .net/ stored procedures to date, just ASP in my webpages.

Many thanks,

GISVPN
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 7

Assisted Solution

by:Cboudroz
Cboudroz earned 175 total points
Comment Utility
I'm not a web developper, I'm dba.

but if you want to used Visual studio 2008 to create  a new application, you should try to learn MVC
http://www.asp.net/get-started



if you want to used a specific technologie google for that tecnologie and MS SQL Store procedure

if you need help on Store procedur create you can ask me your question.
0
 

Accepted Solution

by:
gisvpn earned 0 total points
Comment Utility
Hi thanks for the post, can i ask how suceptable is asp method I am currently using is to code injection attacks?

Regards,

GISVPN
0
 
LVL 2

Assisted Solution

by:KentMarsh
KentMarsh earned 175 total points
Comment Utility

A secure database connection for ASP.NET applications

Use Integrated Security
Your example is problematic because you have a userID and Password as part of your connection code. When you use the Integrated Security option, there is no issue of storing and keeping credentials secure. Use DB Admin and Network Admin tools to manage database access. A connection string can be stored in your web.config file as shown below; where "MyConnectionString" is the name of your connection and "MyDatabase" is the name of the SQL default database used by sql.
(...)
    <appSettings />
    <connectionStrings>
        <add name="MyConnectionString" connectionString="Data Source=D15806;Initial Catalog=MyDatabase;Integrated Security=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
(...)

Open in new window


In your ASP.NET Application you should centralize logic that connects to the database into a single class, say a "MyDatabaseConnection" class and include logic like that shown below.
        public static SqlConnection GetConnection()
        {
            string connStr = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            
            if (conn.State != System.Data.ConnectionState.Open)
            {
                conn.Open();
            }
            
            return conn;
        }

Open in new window


SQL Script manipulation

Don't concatenate strings to create a SQL Statement; Instead - use parameters
Validate parameters to ensure they are of the proper datatype and don't contain SQL injection
Don't take text values directly from a text box and use them in an SQL Statement.
Use an object to pass entity data between various classes (e.g. from UI to business layer)
Below is an example of using parameters.
    public class CompanyBusinessObject
    {
        static public List<CompanyProject> SelectProjects(string companyName)
        {
            List<CompanyProject> items = new List<CompanyProject>();

            SqlConnection conn = CompanyBusinessObject.GetConnection();
            SqlDataReader reader = null;
            try
            {
                conn.Open();
                string sql = "SELECT ProjectNumber FROM CompanyProjects WHERE (Company = @CompanyName) ORDER BY ProjectNumber";
                SqlCommand cmd = new SqlCommand(sql, conn);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@CompanyName";
                param.Value = companyName;
                cmd.Parameters.Add(param);

                reader = cmd.ExecuteReader();

                // write each record
                while (reader.Read())
                {
                    CompanyProject item = new CompanyProject();
                    item.CompanyName = companyName;
                    item.ProjectNumber = reader[0].ToString();

                    items.Add(item);
                }
            }
            catch (SqlException sqlEx)
            {
                // Handle exceptions
            }
            finally
            {
                conn.Close();
            }

            return items;
        }

        public static SqlConnection GetConnection()
        {
            string connStr = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            
            if (conn.State != System.Data.ConnectionState.Open)
            {
                conn.Open();
            }
            
            return conn;
        }
    }

Open in new window

0
 

Author Closing Comment

by:gisvpn
Comment Utility
Thank you for all the comments
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

744 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

15 Experts available now in Live!

Get 1:1 Help Now