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

  • Have you tried sqlcmd -S servername1,8888 -d TESTDB -Q "update test_data SET status = 'HIRED', CN = REPLACE('Testing 'O User', '''', '''''') where employeenumber = '12345';" Presumably, you're doing something to place the single quotes around the text value - just modify that to put the full REPLACE call around all text values instead. – RDFozz Aug 8 '17 at 15:15
  • Sorry - brain fart, of course that won't work, you've still got the lone single quote stuck in the middle of the string. No, I think you're stuck with fixing the value as you add in the string to the dynamic SQL. Where and how are you building that (note that this may mean your question would be better moved to stackoverflow.com). – RDFozz Aug 8 '17 at 17:11

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.