Solved

SQL DB Connection (safest way)

Posted on 2011-02-11
7
435 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
ID: 34873756
0
 
LVL 7

Assisted Solution

by:Cboudroz
Cboudroz earned 175 total points
ID: 34874033
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
ID: 34874806
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 7

Assisted Solution

by:Cboudroz
Cboudroz earned 175 total points
ID: 34875691
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
ID: 34877993
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
ID: 34889657

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
ID: 34941314
Thank you for all the comments
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
The viewer will learn how to count occurrences of each item in an array.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 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

24 Experts available now in Live!

Get 1:1 Help Now