Inserting a Comment text to a table using KSH shell

Question

I have a script where it is necessary to add now in the current statement a received COMMENT text from the user as parameter. Below is an example but it is failing. Can someone help me with this ?

\<sup\>#!/usr/bin/ksh

export COMMENTS=${1}
echo ${COMMENTS}
echo "========================================="

sqlplus -s $CONNECT_STRING \<\<-EOF
whenever sqlerror exit 2;
insert into TEST_CACM (COMMENTS) values ('${COMMENTS}');
commit;
EOF\</sup\>

When we execute it fails because special character.

<kbd>test.ksh "Let's test this  ##@@"
Let's test this ##@@
=========================================
ERROR:
ORA-01756: quoted string not properly terminated</kbd>

Note: The user can be put any comment. Whatever comment the user add should be stored in the table.

Does the answer here help? https://stackoverflow.com/questions/18620893/how-do-i-pass-arguments-to-a-pl-sql-script-on-command-line-with-sqlplus — EdmCoff, Mar 27 '23 at 21:16
@markalex, on this example yes but a user can right anything right? — Carlos Andre Cardoso, Mar 27 '23 at 21:49
If you fully trust your users input - use answer suggested by MT0, if not - create simple python script, that will allow you to pass parameters to query. (or any other language supporting parametrized queries) — markalex, Mar 27 '23 at 22:12

MT0 · Answer 1 · 2023-03-27T22:31:15.103

Either:

Escape the single quote in the string value when you pass it in:
```
test.ksh "Let''s test this  ##@@"
```

Use a q-quoted string (and don't pass the closing expression for the q-quoted string as a sub-string of your argument):

#!/usr/bin/ksh

export COMMENTS=${1}
echo ${COMMENTS}
echo "========================================="

sqlplus -s $CONNECT_STRING \<\<-EOF
whenever sqlerror exit 2;
insert into TEST_CACM (COMMENTS) values (q'[${COMMENTS}]');
commit;
EOF

Or replace all the single quotes with two single quotes:

#!/usr/bin/ksh 

export COMMENTS="$(echo $1 | sed "s/'/''/g")"
echo "$COMMENTS"
echo "========================================="

sqlplus -s $CONNECT_STRING \<\<-EOF
whenever sqlerror exit 2;
insert into TEST_CACM (COMMENTS) values ('${COMMENTS}');
commit;
EOF

Don't use SQL*Plus. Instead write a small application in another language (i.e. Python, Java, C#, etc.) that allows you to use parameterised queries and pass the command-line argument as a bind variable.

Inserting a Comment text to a table using KSH shell

1 Answers1