ORA-O1036: Illegal variable name/number

0

There are alot of posts related to this question but no one work for my case. I am using oracle database with C# on visual studio

void addUser()
{
        OracleCommand cmd = new OracleCommand();
        string query ="INSERT INTO users (user_id, f_name, hash, acc_type, cell_no, country, state, city, zip, address, email, img) VALUES ('" +                         Convert.ToString(username) + "','" + Convert.ToString(f_name) + "','" + password + "','" + acc_type + "','" + contactno + "','" + country + "','" + state + "','" + city + "','" + zip + "','" + address + "','" + email + "',imgByte)";
        OracleCommand sc = new OracleCommand(query, usersdb);
        sc.Parameters.AddWithValue("imgByte", imgByte);
        try
        {
            usersdb.Open();
            sc.ExecuteNonQuery();
            usersdb.Close();
            lblSignupError.Visible = true;
            lblSignupError.Text = "Signed up successfully. You can login now.";

            Clear();
            LoginNow();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
            if (usersdb.State == ConnectionState.Open)
            {
                usersdb.Close();
            }
        }
}

Here is the code for Signup to add user in DB but this error comes

***Exception thrown: 'System.Data.OracleClient.OracleException' in System.Data.OracleClient.dll System.Data.OracleClient.OracleException (0x80131938): ORA-01036: illegal variable name/number

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) at System.Data.OracleClient.OracleParameterBinding.Bind(OciStatementHandle statementHandle, NativeBuffer parameterBuffer, OracleConnection connection, Boolean& mustRelease, SafeHandle& handleToBind) at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals) at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor) at System.Data.OracleClient.OracleCommand.ExecuteNonQuery() at StopNShop.SignUpForm.addUser() in E:\Visual Studio Projects\StopNShop\StopNShop\SignUpForm.cs:line 402***

c#
oracle
desktop
ora-01036
asked on Stack Overflow Jan 10, 2020 by Jamal Ahmad • edited Jan 10, 2020 by jason.kaisersmith

1 Answer

1

You should really be using parameters for all your input values; not only will it be more readable, but it will prevent SQL injection attacks.

In answer to your question, oracle parameters should be prefixed with a colon i.e. :imgByte.

See this example: https://stackoverflow.com/a/11048965/8126362

answered on Stack Overflow Jan 10, 2020 by Johnathan Barclay • edited Jan 10, 2020 by Johnathan Barclay

User contributions licensed under CC BY-SA 3.0