0

I'm trying to use parameterized queries in C# for my job assignment as it seems to be the correct way to guard against SQL injections and simplify code.

When the INSERT command is executed the row is inserted otherwise correctly except the string/varchar column is empty.

I attached the minimum code necessary below. Database column types are as follows:

id_number = int(11), datetime_now = datetime, some_text = varchar(64).

The code is running on Ubuntu 14.04.6 LTS and connecting to local database MySQL 15.1 Distrib 5.5.64-MariaDB. The driver is /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so.

I could only find one example online of this exact problem (no answers): String Parameters not working when using MySql over Odbc

I've been stuck with this for a couple of days now and I'm getting desperate. I don't know how to proceed except abandoning parameterized queries completely. I've tried different overloads of the OdbcParameterCollection.Add() method as well as AddWithValue(), still the results are incorrect. Is it possible there is a problem with the ODBC driver itself?

OdbcCommand command = conn.CreateCommand();
command.CommandText = "INSERT INTO MyTable (id_number, datetime_now, some_text) VALUES (?, UTC_TIMESTAMP(), ?)";
command.Parameters.Add("@id_number", OdbcType.Int).Value = 1;
command.Parameters.Add("@some_text", OdbcType.VarChar).Value = "Example text";
conn.Open();
command.ExecuteNonQuery();

I would expect that the row would be inserted correctly or an error would be given.

Instead the inserted row has

id_number = 1, datetime_now = 2019-08-22 14:05:19, some_text = "".

Edit. Changed code: added "@" to parameter names to exactly follow accepted answers in: How to bind parameters via ODBC C#?. The problem still persists.

Edit2. Added datatable code and information about the connection string.

Database table code is:

MariaDB [myDB]> SHOW CREATE TABLE MyTable;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                        |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| MyTable | CREATE TABLE `MyTable` (
  `id_number` int(11) NOT NULL AUTO_INCREMENT,
  `datetime_now` datetime NOT NULL,
  `some_text` varchar(64) NOT NULL,
  PRIMARY KEY (`id_number`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The connection string uses DSN, so it is:

connString = "DSN=myodbc-mydb;";

/etc/odbc.ini:

[myodbc-mydb]
Driver          = myodbc_mysql
Description     = myODBC
SERVER          = 192.168.24.25
PORT            = 53769
USER            = myUser
Password        = myPassword
Database        = myDB
OPTION          = 3
SOCKET          =

/etc/odbcinst.ini:

[myodbc_mysql]
Description     = ODBC for MySQL
Driver          = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Setup           = /usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so
UsageCount      = 8
Threading       = 0
sdaxc
  • 13
  • 4
  • Possible duplicate of https://stackoverflow.com/a/18082957/10708630 – turanszkik Aug 22 '19 at 15:38
  • It is not a duplicate. The problem is not that I don't know the correct way to do the task, the problem is that using the correct method gives incorrect results. I even changed the parameter names once more by adding "@" before them to follow your linked answers 100%, the result is still the same. – sdaxc Aug 23 '19 at 07:54
  • Can you please share the datatable's code? I'm curious about the types and the structure of the table. **Edit:** And your connection string too please. – turanszkik Aug 23 '19 at 09:11
  • I edited the new info to the post. I have to mention though that the program has a lot of working database commands, and has been running for a long time successfully, so the connection itself definitely works. – sdaxc Aug 23 '19 at 11:44
  • Unfortunatley I haven't been able to reproduce your problem yet however I have found some interesting things about the ADO driver of which you can read about here: [link](https://stackoverflow.com/questions/27492235/indexed-mysql-field-not-saving-value-when-creating-editing-record-through-ado). I would try to get rid of the `id_number` assignment because it's autoincrements itself. Or switching the order of the fields. – turanszkik Aug 23 '19 at 12:22
  • It's not ADO but ODBC driver, I'm sorry my bad. – turanszkik Aug 23 '19 at 13:30
  • Thanks for the help. I'll keep looking into it, although it doesn't seem promising. There's definitely something wrong with the server configuration, perhaps it's the driver. The auto-increment was left by me accidentally when I made the test table from the more complex original table. The point of the column in the sample code was just to show that integer values work, varchar values do not. – sdaxc Aug 24 '19 at 16:39
  • Also, when I use a table with more columns (order mixed), all integer and double values are inserted correctly while all varchar columns are left empty. – sdaxc Aug 24 '19 at 16:46

0 Answers0