Solved

SQL DB Connection (safest way)

Posted on 2011-02-11
7
442 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

617 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