• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

SQL DB Connection (safest way)

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
gisvpn
Asked:
gisvpn
5 Solutions
 
James MurrellProduct SpecialistCommented:
0
 
CboudrozCommented:
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
 
gisvpnAuthor Commented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
CboudrozCommented:
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
 
gisvpnAuthor Commented:
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
 
KentMarshCommented:

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now