SQL Issue - Does not read NULL value

Question

I have an issue when I try to run the code, everything runs well when I set my PASSWORD= '' as the code below.

public int ClearEmployeePasswordById(Employee p)
{
    int result = -1;        
    try
    {               
        string sql = "UPDATE EMPLOYEE SET " +
                     "PASSWORD=''" +
                     "WHERE Employee_Id=" + p.EmployeeId;

        Common.logToFile("PosNet.Data.Sybase.cs", "ClearEmployeePasswordById", sql);
        string r = ExecuteNonQuery(sql);

        if (!string.IsNullOrEmpty(r))
            throw new Exception(r);

        result = 1;
        InsertAuditLog();
    }
    catch (Exception ex)
    {
        Common.logErrorToFile("PosNet.Data.Sybase.cs", "ClearEmployeePasswordById", ex.Message);
        //throw ex;
    }
    return result;
}

But when I change the PASSWORD=NULL at my function with the sql, it shows the error "Reset Password Failed". It just read the empty string but not the NULL value. Why does it happen?

private void btnPasswordReset_Click(object sender, EventArgs e)
{
    try
    {
        string employeeWithoutQuote = lblEmployeeId.Text.Substring(1, 10);
        int employeeId = int.Parse(employeeWithoutQuote);
        Employee employee = MF.BC.DA.GetEmployeeByBarcode(employeeId);
        if (MF.BC.DA.ClearEmployeePasswordById(employee) != 1)
        {
            throw new Exception("Reset Password Failed");
        }
        else
        {
            MessageBox.Show("Reset Password Success");
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Have you debugged? And also you'll need to add the code of the SP — Gilad Green, Aug 16 '16 at 07:43
If you look at the SQL you actually create, it'll read `SET PASWORD=NULLWHERE Employee_Id=...`. — J. Steen, Aug 16 '16 at 07:45
Does the PASSWORD column/field allows NULL value in the database? — Richa Garg, Aug 16 '16 at 07:45
You are not setting your Password field to NULL, you are setting it to an Empty String. NULL is not an empty string — Steve, Aug 16 '16 at 07:47
*In general*, you shouldn't be storing passwords as strings. In most cases, you should be storing a salted cryptographic hash (if this is the common case that the password will be prompted for later and all you need to perform is an equality check). — Damien_The_Unbeliever, Aug 16 '16 at 07:55

Gilad Green · Answer 1 · 2016-08-16T07:59:48.870

The problem is that you need to have a space after the PASSWORD=NULL and the WHERE (you probably have written a log for that in your catch stating invalid syntax near ' or similar):

string sql = "UPDATE EMPLOYEE SET " +
             "PASSWORD = NULL "
             "WHERE Employee_Id=" + p.EmployeeId;

But - Avoid using string concatenation for creating sql queries all together. It is susceptible for SQL Injections. Instead use Parameterized Queries

score 2 · Accepted Answer · answered Aug 16 '16 at 07:46

2

Changes your SQL str to this:

string sql = 
    "UPDATE EMPLOYEE SET " +
    "PASSWORD=NULL " + //NOTE: Added a space
    "WHERE Employee_Id=" + p.EmployeeId;

Your final SQL is wrong without the space in place.

answered Aug 16 '16 at 07:46

webnoob

15,747
13
83
165

owh I see that, no wonder. Thank you for sharing your knowledge – Muhammad Saifullah Aug 16 '16 at 08:00

SQL Issue - Does not read NULL value

2 Answers2