CODEWITHSUNDEEP
c#mysqlsql
Gabriel Luca
Gabriel Luca

Reputation: 215

SQL special characters escaping from input

In my database I have some file paths, at some point I need to update part of the path. How to deal with special characters for ex. in \ruba to \rubla when updating data.

\r is special char

\rubla comes from C# input param to SQL query

SQL:

update table
set Location = CONCAT('gabiluca.at.gmail.com\folder', 
                       substring(Location, length('gabiluca.at.gmail.com\director') + 1))
where Location like 'gabiluca.at.gmail.com\\\\folder%'

\\\\translates to \

Simple case:

In a mysql table you have a windows relative path stored in a column named Location. 

Id  Name    Location
38  MuseScore 3.lnk gabiluca.at.gmail.com
39  redenumit   gabiluca.at.gmail.com
40  interior    gabiluca.at.gmail.com\redenumit
41  FileZilla Client.lnk    gabiluca.at.gmail.com\redenumit
42  MuseScore 3.lnk gabiluca.at.gmail.com\redenumit\interior

Now try to update: 'redenumit' in mysql with an update statement on all records. Dose it work ? Keep the prefix. Think of it like when you rename the folder 'redenumit' to 'changed' for example.

I'm not even able to query like this (this is the problem):

select * 
from MediaResource
where Location like 'gabiluca.at.gmail.com\redenumit%'

Upvotes: 2

Views: 183

Answers (1)

sabhari karthik
sabhari karthik

Reputation: 1371

Can you try this command:

SET sql_mode = NO_BACKSLASH_ESCAPES;

and reset:

SET sql_mode = '';

whenever the processing is done?

Upvotes: 1

Related Questions