Save and load sql database to and from file

Question

I am writing C# application to manage passwords. For now, my database is .txt file, but I want to create few ways to store it. Is .mdf file a good way to do it? I know how to manage sql database in .mdf file, but I have no idea how to Save As to location chosen by user or How to load it again to program.

Ofcourse it can be any different file type. I look for most secure way to do it. In future I will create asp.net application and Xamarin android application to manage this on few platforms.

@Edit I will try to explain this one more time. Check this:

User execute program. There are option to create new database or load existing. In creation mode user can set type of database. Now i've got only one and it's txt. I am wondering about other, f.e. .mdf, but I didnt know how to save .mdf file in location selected by user. Any other suggestions about possible extensions and how to secure it are appreciated.

I want to store it as following:

Login | Password | linkForWebsite|

Ofcourse i will hash everything, so if any1 open .txt file, he wont see anything.

Suggestions for hashing this string (not md5. it must be 2 way) also welcome.

Answer 1

Based on my comment above, here what you can do

your application will read from a List<SiteDetail> where Site Detail is

[Serializable()]
public class SiteDetail
{
    public string Login{get; set;}
    public string Password{get; set;}
    public string Url{get; set;}
}

then when you want to store the data ( Save), you do the following

public void Save(string filePath,List<SiteDetail> data)
{
    Stream stream= File.Create(filePath);
    BinaryFormatter serializer = new BinaryFormatter();
    serializer.Serialize(stream, data);
    stream.Close();
}

and when you want to retrieve the data ( Read), you do the following

   public List<SiteDetail> Read(string filePath)
   {
       if (File.Exists(FileName))
        {
            Stream stream= File.OpenRead(filePath);
            BinaryFormatter deserializer = new BinaryFormatter();
            var details= (List<SiteDetail>)deserializer.Deserialize(stream);
            stream.Close();
            return details;
        }
        return null; // file not exists
   }

for encrypting your data check this article Encrypting and Decrypting Data

If you still have any question, go ahead

Edited

for saving meta information about your data as you mentioned in the comments, you can either create another file called meta.dat ( that will store another class [Version,Culture, ....etc ] ), or you can easily add the another class called SiteVersion as following

[Serializable()]
public class SiteVersion : SiteDetail // inherited from SiteDetail to store backup
{
    public int Version{get; set;}
    public string Culture{get; set;}
    public DateTime CreatedOn{get; set;}
}

and in your SiteDetail class, you can add List<SiteVersion> property and you can fill it when ever any changes was made, in this way you can store in your file the details with its version history also

[Serializable()]
public class SiteDetail
{
    public string Login{get; set;}
    public string Password{get; set;}
    public string Url {get;set;}

    public List<SiteVersion> Versions {get; set;}
}

hope it will help you

Answer 2

If you want to save database on .txt file you should follow as:

1. Create text file on specific path such as (C:\bkpDB.txt)

2. Right click On (database name) -> Choose ("Tasks") -> Choose ("Export Data") -> Choose "Next" for "Welcome page" -> Page "Choose a Data Source": do not change anything and go to the next page ->

3. Page "Choose a Destination": [ Destination: "Flat File Destination" ] , [ File name: "C:\bkpDB.txt" ] , [ check "Column names in the first data row" ] and do not change anything else , Choose "Next"

4. Page "Specify Table Copy or Query" select: "Copy data from one or more table" Choose "Next"

5. Page "Configure Flat File Destination": Select table and row and column delimiter Choose "Next" and finally "Finnish"

If you want to load database from .txt file you should follow as:

1. Use text file from specific path such as (C:\bkpDB.txt) that you have filled by previous steps

2. Right click On (database name) -> Choose ("Tasks") -> Choose ("Import Data") -> Choose "Next" for "Welcome page" -> and go to the next page ->

3. Page "Choose a Data Source": [ Data Source: "Flat File Source" ] , [ File name: "C:\bkpDB.txt" ] , [ check "Column names in the first data row" ] and do not change anything else , Choose "Next"

4. Page "Choose a Destination" select: Select your database and Choose "Next" and "Finnish"

If you want to use Encryption and Decryption for password, so I suggest you to use Advanced Encryption Standard AES algorithm

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Web;
using System.Web.Mvc;

namespace MvcWebRole1.Controllers
{
    public class AESController : Controller
    {
        //
        // GET: /AES/

        public ActionResult Index()
        {
            ViewData["Encrypted"] = TempData["TEncrypted"];
            ViewData["Decrypted"] = TempData["TDecrypted"];
            return View();
            
        }

        //Text is PlainText
        //Key is Public Secret Key 
        [HttpPost]
        public ActionResult Encryption(string Text, string Key)
        {
            // Convert String to Byte

            byte[] MsgBytes = Encoding.UTF8.GetBytes(Text);
            byte[] KeyBytes = Encoding.UTF8.GetBytes(Key);

            // Hash the password with SHA256
            //Secure Hash Algorithm
            //Operation And, Xor, Rot,Add (mod 232),Or, Shr
            //block size 1024
            //Rounds 80
            //rotation operator , rotates point1 to point2 by theta1=> p2=rot(t1)p1
            //SHR shift to right
            KeyBytes = SHA256.Create().ComputeHash(KeyBytes);

            byte[] bytesEncrypted = AES_Encryption(MsgBytes, KeyBytes);

            string encryptionText = Convert.ToBase64String(bytesEncrypted);



            TempData["TEncrypted"] = encryptionText;
            return RedirectToAction("Index");
        }

        public byte[] AES_Encryption(byte[] Msg, byte[] Key)
        {
            byte[] encryptedBytes = null;

            //salt is generated randomly as an additional number to hash password or message in order o dictionary attack
            //against pre computed rainbow table
            //dictionary attack is a systematic way to test all of possibilities words in dictionary wheather or not is true?
            //to find decryption key
            //rainbow table is precomputed key for cracking password
            // Set your salt here, change it to meet your flavor:
            // The salt bytes must be at least 8 bytes.  == 16 bits
            byte[] saltBytes = new byte[] { 1, 2, 3, 4, 5, 6, 7, 8 };

            using (MemoryStream ms = new MemoryStream())
            {
                using (RijndaelManaged AES = new RijndaelManaged())
                {
                    AES.KeySize = 256;
                    AES.BlockSize = 128;

                    var key = new Rfc2898DeriveBytes(Key, saltBytes, 1000);
                    AES.Key = key.GetBytes(AES.KeySize / 8);
                    AES.IV = key.GetBytes(AES.BlockSize / 8);

                    AES.Mode = CipherMode.CBC;

                    using (var cs = new CryptoStream(ms, AES.CreateEncryptor(), CryptoStreamMode.Write))
                    {
                        cs.Write(Msg, 0, Msg.Length);
                        cs.Close();
                    }
                    encryptedBytes = ms.ToArray();
                }
            }

            return encryptedBytes;
        }

        [HttpPost]
        public ActionResult Decryption(string Text2, string Key2)
        {
            // Convert String to Byte
            byte[] MsgBytes = Convert.FromBase64String(Text2);
            byte[] KeyBytes = Encoding.UTF8.GetBytes(Key2);
            KeyBytes = SHA256.Create().ComputeHash(KeyBytes);

            byte[] bytesDecrypted = AES_Decryption(MsgBytes, KeyBytes);

            string decryptionText = Encoding.UTF8.GetString(bytesDecrypted);


            TempData["TDecrypted"] = decryptionText;
            return RedirectToAction("Index");
        }

        public byte[] AES_Decryption(byte[] Msg, byte[] Key)
        {
            byte[] decryptedBytes = null;

            // Set your salt here, change it to meet your flavor:
            // The salt bytes must be at least 8 bytes.
            byte[] saltBytes = new byte[] { 1, 2, 3, 4, 5, 6, 7, 8 };

            using (MemoryStream ms = new MemoryStream())
            {
                using (RijndaelManaged AES = new RijndaelManaged())
                {
                    AES.KeySize = 256;
                    AES.BlockSize = 128;

                    var key = new Rfc2898DeriveBytes(Key, saltBytes, 1000);
                    AES.Key = key.GetBytes(AES.KeySize / 8);
                    AES.IV = key.GetBytes(AES.BlockSize / 8);

                    AES.Mode = CipherMode.CBC;

                    using (var cs = new CryptoStream(ms, AES.CreateDecryptor(), CryptoStreamMode.Write))
                    {
                        cs.Write(Msg, 0, Msg.Length);
                        cs.Close();
                    }
                    decryptedBytes = ms.ToArray();
                }
            }

            return decryptedBytes;
        }
    }
}

In View Section ("Index"):

@{
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
}


<h2>Encryption And Decryption</h2>

<div style="color:red;" id="EncryptedText">Encrypted Message: @ViewData["Encrypted"]</div>

<br />
<div style="color:red;" id="DecryptedText">Decrypted Message: @ViewData["Decrypted"]</div>
<br />


    @using(Html.BeginForm("Encryption", "AES", FormMethod.Post))
    {
         <label id="lbk1">Key:</label><input name="Key" id="Key" type="text" />
        <br />
         <label id="lbk2">Message:</label><input name="Text" id="Text" type="text" />
        <br />
        <input id="btnEncryption" type="submit" value="Encryption" />
        <br />  
        <br />          
    }

@using(Html.BeginForm("Decryption", "AES", FormMethod.Post))
    {
        <label id="lbk4">Enter Same Key:</label><input name="Key2" id="Key2" type="text" />
        <br />
         <label id="lbk5">Enter Encrypted Message:</label><input name="Text2" id="Text2" type="text" />
        <br />
	    <input id="btnDecryption" type="submit" value="Decryption" />
	
    }


<br />

Edit: (Create database (".mdf") from application by user)

To create database inside application you can use stored procedure from another database and call this sp when user wants to create database (.mdf) and pass value such as database name and etc.

CREATE PROC dbo.dbbase
AS

DECLARE  @db varchar(50), @sql varchar(1000)
    
    IF DB_ID(@db) IS NULL
    SELECT @SQL = 'CREATE DATABASE ' + @db + 
                ' ON (NAME = ' + @db + '_Data, FILENAME = ''C:\UserName\' + @db + '.mdf'')' +
                ' LOG ON (NAME = ' + @db + '_Log, FILENAME = ''C:\UserName\' + @db + '.mdf'')'

    EXEC(@sql)

GO

I hope it works for you.