Why inserting null values while adding parameter sqldbtype.nvarchar value?

Question

0.00/5 (No votes)

See more:

I become failed to add user with password where SqlDataType is nvarchar(max).

In my Inserting Code:

C#

private void btnAdd_Click(object sender, EventArgs e)
        {
            try
            {
                db.command.Parameters.Add("@username", SqlDbType.VarChar).Value = txtUserName.Text.ToString();
                db.command.Parameters.Add("@password", SqlDbType.NVarChar).Value = txtPassword.Text.ToString();
                db.command.Parameters.Add("@status", SqlDbType.Bit).Value = 1;
                db.Adapter("addUser", true);
            }
            catch(Exception err)
            {
                MessageBox.Show(err.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

In my store procedure:

SQL

USE [product_red_sky]
GO
/****** Object:  StoredProcedure [dbo].[addUser]    Script Date: 06/04/2016 16:06:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[addUser]
(
    @username VARCHAR(50), 
    @password NVARCHAR(max), 
    @status bit
    
)
AS
BEGIN
    
        INSERT INTO tbllogin (username, password_user, status_user)
        VALUES(@username, HASHBYTES('SHA2_512', @password), @status)

END

But, in Database Table(tbllogin) I am getting null values in password_user fields. Please help me to Convert String to NVarChar in c# code.

What I have tried:

I have increased the user_password filed in max in tbllogin and also in store-procedure addUser.

Posted 4-Jun-16 0:15am

Member 10261487

Updated 4-Jun-16 0:28am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2016-06-04T00:28:00

Solution 1

Don't do the SHA in SQL - do it in your C# code, and use a Salt value (such as the username) to prevent identical passwords having identical hashes. Then pass the byte array from the hash directly to SQL instead of the string. This may help: Password Storage: How to do it.[^]

C#

SHA512CryptoServiceProvider sha = new SHA512CryptoServiceProvider();
byte[] hashvalue = sha.ComputeHash(System.Text.Encoding.Unicode.GetBytes(txtUserName.Text + txtPassword.Text));
db.Command.Parameters.AddWithValue("@password", hashvalue);

Then declare the Stored Procedure parameter as VARBINARY(64) and it should all work.

Posted 4-Jun-16 0:28am

OriginalGriff

Updated 4-Jun-16 0:29am

v2

Comments

Member 10261487 4-Jun-16 8:15am

Well, thank you very much. This working fine. Now, Let me know how can I check login there. I have tried as like this:
public static bool Login(string username, string password)
{
bool result;
SqlDBAccess db = new SqlDBAccess();
db.Adapter("select * from tbllogin where username = '" + username.ToString() + "'", "login_info");
try
{
if (db.dataSet.Tables["login_info"].Rows[0][2].ToString() == "0")
{
result = false;
}
else
{
byte[] hashedPassword = GetSHA1(username.ToString(), password.ToString());
byte[] dbhashedPassword = Convert.FromBase64String(db.dataSet.Tables["login_info"].Rows[0][1].ToString());
result = MatchSHA1(hashedPassword, dbhashedPassword);
}
return result;
}
catch(Exception err)
{
MessageBox.Show(err.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
result = false;
return result;
}
}

private static bool MatchSHA1(byte[] p1, byte[] p2)
{
bool result = false;
if (p1 != null && p2 != null)
{
if (p1.Length == p2.Length)
{
result = true;
for (int i = 0; i < p1.Length; i++)
{
if (p1[i] != p2[i])
{
result = false;
break;
}
}
}
}
return result;
}

private static byte[] GetSHA1(string userID, string password)
{
SHA1CryptoServiceProvider sha = new SHA1CryptoServiceProvider();
return sha.ComputeHash(System.Text.Encoding.ASCII.GetBytes(userID + password));
}

OriginalGriff 4-Jun-16 8:41am

For starters, DON'T DO IT LIKE THAT!
Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
This is a login screen - so anyone can type what they like in your username box, and string concatenation just gives them complete control over your DB...
I know you know what parameterized queries are - you already use them - so why suddenly risk SQL Injection?
If you have a method you can pass a username and password, and have it generate a hash, why duplicate the same code instead of using it?
And if you read the tip I linked to it explains how to check it. Don't just copy code and paste it into your app, you have to think about how you use it as well.