Problem with update command. Replacing ":" into "@"

Data Abstract
,
1

Hi.

Actually, we’re facing a problem when exectuting SQL UPDATE when we have a field with text content with “:” character. Follow the code snippet on Oxygene project:

     {
    	Comunication
     }
     daconn : IAbstractConnection; 

method TDAADOConn.execSQL (sql: String): Integer;

var  cmd : IDbCommand;

begin

  try
   
    cmd := daconn.NewCommand(sql);
    Result := cmd.ExecuteNonQuery;

When executed a UPDATE like that:

UPDATE TABLE T SET T.MESSAGE = “Today is: 05/05/2021 01:04:05” WHERE T.ID = 1;

After parse NewCommand(sql), the text changes from “Today is: 05/05/2021 01:04:05” to “Today is@ 05/05/2021 01@04@05” and apply to the database.

On other hand, an INSERT preserve the Original Text, passing by the same command!

Is there some command to preserve the Original text? Keeping : instead replace it to @?

OS Version: Windows 10
DataAbstract Version: RemObjects Data Abstract for Water and Visual Studio, Server Edition - 10.0.0.1495
IDE: Visual Studio 2019 and Water.
DataBase: MySQL 5.7 using DB Connector: mysql-connector-net-8.0.25

Thanks

3

Hello

For My SQL you need to use ' to define string literals, not ":

UPDATE TABLE T SET T.MESSAGE = 'Today is: 05/05/2021 01:04:05' WHERE T.ID = 1;

While MySQL itself sometimes allows to use " for string literals as well, it depends on server settings:
https://dev.mysql.com/doc/refman/8.0/en/string-literals.html

So Data Abstract uses ' as string delimiter

5

Hi

Sorry. This is the exacts commands before cmd.newcommand and after it.

– INSERT
(Before) sql = ‘INSERT INTO TABLE (MESSAGE) VALUES (’‘Today is: 05/05/2021 01:04:05’‘)’
(After) CommandText = ‘INSERT INTO TABLE (MESSAGE) VALUES (’‘Today is: 05/05/2021 01:04:05’‘)’

– UPDATE
(Before) sql = ‘UPDATE TABLE T SET T.MESSAGE = “Today is: 05/05/2021 01:04:05” WHERE T.ID = “1”’
(After) CommandText = ‘UPDATE TABLE T SET T.MESSAGE = “Today is@ 05/05/2021 01@04@05” WHERE T.ID = “1”’

Thank you.

6

For My SQL you need to use ' to define string literals, not "

Also it is highly recommended to use parametrized commands instead of plain string concatenation. Otherwise your code might have the SQL Injection vulnerability.

7

Hi

We have parameterized strings for compound the command. The problem is related to the UPDATE Command. This was just an example showing the problem!

Thanks.

8

Hi

I`ve done a new test and the problem was identified.

There was 2 separate quotes ’ ’ instead just one "

Thank you!.