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...


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?

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.

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 :)

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:


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

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:


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

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:


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.


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();

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!

Can anyone clue me in here? I've spent 10+ hours on it.

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.

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.

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..


// 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();
}

Still need help here..

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.