Sql-server – Update thesql DB when data has a single quote with Dynamic values

MySQLsql serversqlcmd

I am writing data to mysql DB from my linux machine using sqlcmd. My script is updating the data Dynamically based on the employee number. I have a situation where the the CN attribute might have single quote and the update fails for that employee record. How can the below command modified to ignore the single quote. I know by using a double single quote can do the job for a single entry and there are many solution for this that has two or three line sql command to achieve the task, please note I am using sqlcmd to update my data and the data is Dynamic, so am looking for a one line command to update the data

Below I have an employee where the CN has value "Testing 'O User", so SQL fails to update. I know I can use a single quote in front of O and that should do the job "Testing ''O User"…..But think of Dynamic Values I may not able to go to each record and do that…Need your help please…

sqlcmd -S servername1,8888 -d TESTDB -Q "update test_data SET status = 'HIRED', CN = 'Testing 'O User' where employeenumber = '12345';"

Below is the error:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'O'.
Msg 105, Level 15, State 1, Line 6
Unclosed quotation mark after the character string ';
'.

Best Answer

NOTE: Several questions/comments that wouldn't fit into a comment block ...

What language is your linux script written in? bash? ksh? perl? something else?

Where is the CN value coming from and how are you handling it, eg, is the CN value initially stored in a variable which is then used to build the actual query?

Have you tried passing in the CN value by way of a variable (either sqlcmd/-v or direct OS variable reference like "update ... CN = '${CN}'...")? [I'm guessing it still fails but want to confirm this.]

Could you pre-process the CN value, replacing any occurrences of a single quote with either a) two single quotes ('') or b) a backslash plus single quote (\'); then feed this resulting value into your dynamically created query? [Depending on your scripting language, and how you're handling the CN value, this may be doable with littler overhead.]

While your example demonstrates the issue, it doesn't show us how you're building the query itself ... so I'm wondering if there could be a (relatively) easy solution that, in effect, allows us to escape the single quote while building the query ... ?

Related Question