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
Solved

SQL DB Connection (safest way)

Posted on 2011-02-11
7
438 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

829 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