Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2002
    Location
    Southwest Michigan
    Posts
    935

    Newbie C# Question -- Using Parameters with MySQL INSERT

    Hey guys --

    Stuck agan! I've been dinking with protecting againts injection thru using parameters, but I can't get it to work how I want. Here's the code...

    Code:
    Cmd.Parameters.Add(new OdbcParameter("sqlListId", "hi"));
    Cmd.CommandText = "INSERT INTO `customers` (`secondary_id`) VALUES( @sqlListId )";
    I'll be using a variable instead of "Hi" when I do get it functioning correctly, but right now when I run the program it will go thru and add a row to the database (42 in total), but it will not put anything in the `secondary_id` field.

    What am I doing wrong?

  2. #2
    Join Date
    Aug 2004
    Location
    Tulsa, Oklahoma
    Posts
    169
    Just curious why you have single ticks around your field name and your table name? I don't have anywhere near the experience in MySQL that I do in MS-SQL, so I may be way off.
    Michael B

  3. #3
    Join Date
    Jul 2001
    Location
    Glasgow, Scotland
    Posts
    130
    My C is a bit rusty to say the least, but I'm fairly updated on MySQL, and your query looks a bit weird IMO..

    INSERT INTO `customers` (`secondary_id`) VALUES( @sqlListId );
    I'd try something like;
    INSERT INTO customers ('secondary_id') VALUES (@sqlListId);

    @sqlListId isn't an array is it? You can only insert one string at a time in one query. Perhaps I'm messing up my C with Perl here, been working way too much in perl lately! >_<
    Anyhow, good luck
    My development blog - (un)Interesting codesnippets and the occational code-related rant!

  4. #4
    Join Date
    Aug 2000
    Location
    Redmond, WA
    Posts
    310
    Try @sqlListId instead of sqlListId in the parameter ObdcParameter declaration. It might seem odd, but that's what normally works with MSSQL Stored Procedures. Your code would look something like this:

    Code:
    Cmd.Parameters.Add(new OdbcParameter("@sqlListId", "hi"));
    Cmd.CommandText = "INSERT INTO `customers` (`secondary_id`) VALUES( @sqlListId )";
    Chris Spangler
    chris@thespanglers.net

  5. #5
    Join Date
    Aug 2004
    Location
    Tulsa, Oklahoma
    Posts
    169
    ahhh good call.

    Originally posted by banner
    Try @sqlListId instead of sqlListId in the parameter ObdcParameter declaration. It might seem odd, but that's what normally works with MSSQL Stored Procedures. Your code would look something like this:

    Code:
    Cmd.Parameters.Add(new OdbcParameter("@sqlListId", "hi"));
    Cmd.CommandText = "INSERT INTO `customers` (`secondary_id`) VALUES( @sqlListId )";
    Michael B

  6. #6
    Join Date
    Aug 2002
    Location
    Southwest Michigan
    Posts
    935
    Originally posted by banner
    Try @sqlListId instead of sqlListId in the parameter ObdcParameter declaration. It might seem odd, but that's what normally works with MSSQL Stored Procedures. Your code would look something like this:

    Code:
    Cmd.Parameters.Add(new OdbcParameter("@sqlListId", "hi"));
    Cmd.CommandText = "INSERT INTO `customers` (`secondary_id`) VALUES( @sqlListId )";
    yeah I've actually tried that but it still comes back as NULL in the database. Any other ideas?


    Here's the entritey of my SQL code, save the connection string. In actuallity this code is spread out thru-out the name space, but for simplicities sake I'm only posting the relevant information right here.

    Code:
    OdbcConnection _Conn = new OdbcConnection(MyConString);  
    _Conn.Open();
    OdbcCommand Cmd = new OdbcCommand("",_Conn);
    Cmd.Parameters.Add(new OdbcParameter("@sqlListId", "hi"));
    Cmd.CommandText = "INSERT INTO `customers` (`secondary_id`) VALUES( @sqlListId )";
    Cmd.ExecuteNonQuery();
    Last edited by FW-Mike; 06-16-2005 at 09:44 AM.

  7. #7
    Join Date
    Aug 2002
    Location
    Southwest Michigan
    Posts
    935
    I swear I have tried every possible combination, I've been at this for the last day. I can't get it! Its alwaaayyyss null!

  8. #8
    Join Date
    Aug 2002
    Location
    Southwest Michigan
    Posts
    935
    Can anyone clue me in here? I've spent 10+ hours on it.

  9. #9
    Join Date
    Aug 2002
    Location
    Southwest Michigan
    Posts
    935
    A little more info on what my situation is, I guess...

    The below code DOES WORK. It creates 41 rows each having a secondary ID of TESTER.
    Code:
    string variable = "TESTER";
    Cmd.CommandText = "INSERT INTO `customers` ( `primary_id` , `secondary_id` , `file_as` , `company_name` , `contacts` , `title` , `first_name` , `middle_name` , `last_name` , `suffix` , `phone_numbers` , `cellular_provider` , `email` , `web_page` , `address` , `city` , `state` , `postal_code` , `country` , `memo` , `status` , `time_modified` ) VALUES ('', '"+variable+"', '', NULL , '', '', NULL , '', NULL , '', '', NULL , NULL , NULL , '', '', '', '', '', NULL , '', '')";
    The below code DOES NOT WORK. It creates 41 rows with entirely null or empty values.
    Code:
    string variable = "TESTER";
    Cmd.Parameters.Add(new OdbcParameter("@sqlListId", variable));
    Cmd.CommandText = "INSERT INTO `customers` ( `primary_id` , `secondary_id` , `file_as` , `company_name` , `contacts` , `title` , `first_name` , `middle_name` , `last_name` , `suffix` , `phone_numbers` , `cellular_provider` , `email` , `web_page` , `address` , `city` , `state` , `postal_code` , `country` , `memo` , `status` , `time_modified` ) VALUES ('', @sqlListId, '', NULL , '', '', NULL , '', NULL , '', '', NULL , NULL , NULL , '', '', '', '', '', NULL , '', '')";
    And a bit of the code around it..

    Code:
    // Attempt to locate the customer from QB in JTO by secondary ID
    Cmd.CommandText = "SELECT * FROM `customers` WHERE `secondary_id` = '"+ListID+"' LIMIT 1";
    OdbcDataReader MyDataReader = Cmd.ExecuteReader();
    								
    // If the customer is found, begin update
    if(MyDataReader.HasRows)
    {
    //do stuff
    }
    else
    {
    MyDataReader.Close();
    string variable = "TESTER";
    Cmd.Parameters.Add(new OdbcParameter("@sqlListId", variable));
    Cmd.CommandText = "INSERT INTO `customers` ( `primary_id` , `secondary_id` , `file_as` , `company_name` , `contacts` , `title` , `first_name` , `middle_name` , `last_name` , `suffix` , `phone_numbers` , `cellular_provider` , `email` , `web_page` , `address` , `city` , `state` , `postal_code` , `country` , `memo` , `status` , `time_modified` ) VALUES ('', @sqlListId, '', NULL , '', '', NULL , '', NULL , '', '', NULL , NULL , NULL , '', '', '', '', '', NULL , '', '')";
    Cmd.ExecuteNonQuery();
    }
    Last edited by FW-Mike; 06-17-2005 at 10:56 AM.

  10. #10
    Join Date
    Aug 2002
    Location
    Southwest Michigan
    Posts
    935
    Still need help here..

  11. #11
    Join Date
    Mar 2004
    Location
    california
    Posts
    162
    Have you tried something like this?

    //Test can be any value
    string Test = "hi";
    //the following code inserts the Test value, replacing a single quote with two single quotes to prevent sql injection
    string sql = string.Format("INSERT INTO customers ( secondary_id ) VALUES ( '{0}' )", Test.Replace("'", "''"));

    I think that should work, at least in SQL Server. Do you replace single quotes with double quotes? or with a \'? It's been a while since I've worked with mySQL.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •