Update mysql DB when data has a single quote with Dynamic values
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 ';
'.
mysql sqlcmd
edited Aug 8 '17 at 15:09
RDFozz
9,82731430
asked Aug 8 '17 at 14:07
Binish BabyBinish Baby
61
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
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 ';
'.
mysql sqlcmd
edited Aug 8 '17 at 15:09
RDFozz
9,82731430
asked Aug 8 '17 at 14:07
Binish BabyBinish Baby
61
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Have you triedsqlcmd -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 fullREPLACEcall 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
add a comment |
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 ';
'.
mysql sqlcmd
edited Aug 8 '17 at 15:09
RDFozz
9,82731430
asked Aug 8 '17 at 14:07
Binish BabyBinish Baby
61
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 ';
'.
mysql sqlcmd
mysql sqlcmd
edited Aug 8 '17 at 15:09
RDFozz
9,82731430
asked Aug 8 '17 at 14:07
Binish BabyBinish Baby
61
edited Aug 8 '17 at 15:09
RDFozz
9,82731430
asked Aug 8 '17 at 14:07
Binish BabyBinish Baby
61
edited Aug 8 '17 at 15:09
RDFozz
9,82731430
edited Aug 8 '17 at 15:09
RDFozz
9,82731430
edited Aug 8 '17 at 15:09
RDFozz
9,82731430
9,82731430
asked Aug 8 '17 at 14:07
Binish BabyBinish Baby
61
asked Aug 8 '17 at 14:07
Binish BabyBinish Baby
61
asked Aug 8 '17 at 14:07
Binish BabyBinish Baby
61
61
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Have you triedsqlcmd -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 fullREPLACEcall 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
add a comment |
Have you triedsqlcmd -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 fullREPLACEcall 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
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
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
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
add a comment |
1 Answer
1
active
oldest
votes
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 ... ?
answered Aug 11 '17 at 0:22
markpmarkp
2,0721214
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f182962%2fupdate-mysql-db-when-data-has-a-single-quote-with-dynamic-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 ... ?
answered Aug 11 '17 at 0:22
markpmarkp
2,0721214
add a comment |
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 ... ?
answered Aug 11 '17 at 0:22
markpmarkp
2,0721214
add a comment |
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 ... ?
answered Aug 11 '17 at 0:22
markpmarkp
2,0721214
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 ... ?
answered Aug 11 '17 at 0:22
markpmarkp
2,0721214
answered Aug 11 '17 at 0:22
markpmarkp
2,0721214
answered Aug 11 '17 at 0:22
markpmarkp
2,0721214
answered Aug 11 '17 at 0:22
markpmarkp
2,0721214
2,0721214
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f182962%2fupdate-mysql-db-when-data-has-a-single-quote-with-dynamic-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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 fullREPLACEcall 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