0

I have a problem with a huge project that I am working on. I never had this problem before but it started occurring today. Any fixes?

error: System.Data.SqlClient.SqlException: 'The data types text and nvarchar are incompatible in the equal to operator.'

I don't understand what It means. I don't want to have an sql injection so I am using parameters.. But it still gives errors.

Please ask me if you need me to describe something... This is like my 5th post.


            using (SqlCommand StrQuer = new SqlCommand("SELECT * FROM login WHERE username=@userid AND password=@password", connection))
            {
                StrQuer.Parameters.AddWithValue("@userid", username.Text.ToLower().Trim());
                StrQuer.Parameters.AddWithValue("@password", obf.Obfuscate(password.Text).ToString());
                SqlDataReader dr = StrQuer.ExecuteReader();
                if (dr.HasRows)
                {
                    Properties.Settings.Default.username = username.Text.ToLower();
                    Properties.Settings.Default.username = obf.Obfuscate(password.Text);
                    RegistryKey key = Registry.CurrentUser.CreateSubKey(@"SOFTWARE\howcoolitisX");
                    key.SetValue("username", username.Text.ToLower());
                    key.SetValue("password", obf.Obfuscate(password.Text));
                    key.Close();
                    Form executor = new Loader();
                    executor.Show();
                    this.Hide();
                }
            }
frionx
  • 15
  • 7
  • forgot to say! the obfuscate function just does a base64 obfucation ```cs class Obfuscation { public string Obfuscate(string source) { return Convert.ToBase64String(Encoding.UTF8.GetBytes(source)).ToString(); }``` – frionx Nov 29 '19 at 21:54
  • 3
    The `text` data type is not appropriate for a password, you should change it to `nvarchar`. As an aside, base64 encoding does not provide any security. – Crowcoder Nov 29 '19 at 22:03
  • I know. Base64 decoders are online. that's not what im worried about. Im worried about the sql not working atm. But what do you mean change it to nvarchar, the code or database? – frionx Nov 29 '19 at 22:10
  • 1
    I mean the database. – Crowcoder Nov 29 '19 at 22:11
  • `Properties.Settings.Default.username = obf.Obfuscate(password.Text);` ??? Typo? Or are the users forced to set their username as the password? – LukStorms Nov 29 '19 at 22:23
  • typo but thx for pointing out – frionx Dec 01 '19 at 22:53

1 Answers1

3

Well, first I'd say that you should probably take a look at how can passwords be stored securely.

And as for the issue at hand there are multiple solutions

  1. Either cast the parameters in SQL
new SqlCommand("SELECT * FROM login WHERE username= CAST(@userid as text) AND password= CAST(@@passwordas text)", connection)
  1. Or use Parameters.Add instead of Parameters.AddWithValue and set the parameter type explicitly.
StrQuer.Parameters.Add("@userid", SqlDbType.Text);
command.Parameters["@userid"].Value = username.Text.ToLower().Trim();
  1. Or if you can easily change the database then by all means do as Crowcoder suggested and change columns to nvarchar as text is an obsolete datatype

IMPORTANT! ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Eugene Podskal
  • 10,270
  • 5
  • 31
  • 53