locked
Data from a SQL Database RRS feed

  • Question

  • User-372071297 posted

    Hi Folks,

    i have a SQL (Express) Database and I like to use it in my MVC Web App. Sorry, all for me is new. I´m learning building up websites. 

    The normal way I connect into my database, is with username and password. But already, when I try to insert these database in my MVC Project I get a warning message that told me, that ther is a security problem, when userdates are a part of the connection string. I already told you, that I am a absolute beginner in these points, so please tell me, what is the correct way to get datas from and to a sql database by a secure way.

    Thanks alot.

    datekk.

    Friday, March 10, 2017 2:30 PM

All replies

  • User-1509636757 posted

    You may use Entity Framework as middleware between Database and your code to access and manipulate data. Can you provide some more detail on how you are trying to access data from database? Also, let all know if you are facing any specific issue while accessing data.

    Friday, March 10, 2017 4:01 PM
  • User-372071297 posted

    Hi. Thanks for answer. I work with Visual Stuido and Im using .Net MCV.. I have insert a Model, based on EF. After scarefoldering I have new entries in my Web.config file:

    <connectionStrings>

    <add name="PersonendatenbankEntities" cconnectionString="metadata=res://*/Models.SQLModel.csdl|res://*/Models.SQLModel.ssdl|res://*/Models.SQLModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=*****************;initial catalog=Test_db;persist security info=True;user id=***********;password=**********************;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
    </connectionStrings>

    But Visual Studio told me, that way is not secure.

    Friday, March 10, 2017 4:22 PM
  • User-1509636757 posted

    Storing password in web.config is of course a security risk, specially when you are on shared hosting for deployment. However, you can chose not to store password in web.config. In that case, you will require to manipulate the web.config connection string to add password dynamically when you are creating object of your database context to access data in Entity Framework.

    To do this, chose not to store password while creating Entity Model:

    Create a partial class that inherits your database entity class. In this class, add a parameterised constructor that accepts password and access connection string from web.config (that is without password) and rebuilds connection string by filling up password in there and return the entity object to access your data. Here I am using Northwind database to represent the class:

    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data.Common;
    using System.Data.Entity.Core.EntityClient;
    using System.Linq;

    public partial class DBEntities : NorthwindEntities { public DBEntities(string password) : base() { if (string.IsNullOrEmpty(password)) throw new Exception("Please provide password to connect to database!"); else { var originalConnectionString = ConfigurationManager.ConnectionStrings["NorthwindEntities"].ConnectionString; var entityBuilder = new EntityConnectionStringBuilder(originalConnectionString); var factory = DbProviderFactories.GetFactory(entityBuilder.Provider); var providerBuilder = factory.CreateConnectionStringBuilder(); providerBuilder.ConnectionString = entityBuilder.ProviderConnectionString; providerBuilder.Add("Password", password); Database.Connection.ConnectionString = providerBuilder.ToString(); } } }

    Finally, this is how you will call it:

    protected void Page_Load(object sender, EventArgs e)
    {
        string password = "123"; //-- passing password from your code
        //-- here you can have a encrypted password and write code to decrypt logic to get the actual password and then pass it to create context object
        using (DBEntities context = new DBEntities(password))
        {
            List<Category> categoryList = context.Categories.ToList();
        }
    }

    Friday, March 10, 2017 5:18 PM