Oracle sql statement parameters errors in c#

Ken H

hi,
try
{

OracleCommand cmd = new OracleCommand();
conn.Open();
using (cmd = conn.CreateCommand())
{

cmd.CommandText = "select count(*) from User_Info where Password=@p";
cmd.Parameters.Add(new OracleParameter("@u",this.txtUser.Text));
cmd.Parameters.Add(new OracleParameter("@p",this.txtPassword.Text));
int i = Convert.ToInt32(cmd.ExecuteScalar());
if (i > 0)
{
this.Hide();
conn.Close();
}
else
{
conn.Close();
MessageBox.Show("the username or password is error!");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
conn.Close();
}
Run-time, display the "illegal variable name / number"
thank very much.

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes14y ago
Accepted answer
I think you're supposed to use : rather than @ as the prefix for Oracle parameters. So I'd try replacing @u with :u and @p with :p throughout the code.
Vulpes14y ago
I realize that you're at risk of SQL injection if you don't use parameters but, if we can't figure out what's wrong with it, you don't have much choice.

Incidentally, which Oracle Data Provider are you using here?

If we can find some documentation for it, then it might give us a clue what's wrong.
Ken H14y ago
However, if you do not use parameters it will bring insecurity. Such as sql security vulnerabilities
SQL> select * from user_info
2 /
ID ACCOUNT PASSWORD
-------- -------- ---------------
sp sp_user spadmin

such as,i input A'or'A'='A in password text,Can also login. Will result in security vulnerabilities.
Vulpes14y ago
I don't think there's a problem in a column having the same name as a reserved word. It's when you try to use reserved words in queries when the problems begin.

However, if it's still not working I don't know what else to suggest apart from not using parameters at all.
Ken H14y ago
hi Vulpes,
i not think the password as column name is reserved words in oracle,because i can use password in entity table oracle.
such as:
SQL> select * from user_info;
id password
ab ab1234
ac ac1234
2 rows selected.

thanks.
Vulpes14y ago
I've just been looking at a list of reserved words in Oracle and apparently Password is one of them:

http://docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm

So I'd try escaping Password by enclosing it in double quotes:

cmd.CommandText = "select count(*) from User_Info where ID=:u and \"Password"\=:p";
Ken H14y ago
still can not solve the problem.
Vulpes14y ago
Does it make any difference if you keep the : prefix in the Select statement (I'm as sure as I can be that it's needed there) but don't use it - or anything else -in the statements where the parameters are added:

cmd.Parameters.Add(new OracleParameter("u",this.txtUser.Text));
cmd.Parameters.Add(new OracleParameter("p",this.txtPassword.Text));
Ken H14y ago
replacing with:u and with :p
It is equally wrong.
try
            {

                OracleCommand cmd = new OracleCommand();
                conn.Open();
                using (cmd = conn.CreateCommand())
                    {

                      cmd.CommandText = "select count(*) from User_Info where ID=:u and Password=:p";
                        cmd.Parameters.Add(new OracleParameter(":u",this.txtUser.Text));
cmd.Parameters.Add(new OracleParameter(":p",this.txtPassword.Text));
int i = Convert.ToInt32(cmd.ExecuteScalar());
                        if (i > 0)
                        {
                            this.Hide();
                            conn.Close();
                        }
                        else
                        {
                            conn.Close();
                            MessageBox.Show("the username or password is error!");
                        }
                    }
                }
           catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                conn.Close();
            }