 Which is better - CHR(39) or '''' ? [message #167465] |
Thu, 13 April 2006 03:53  |
sonic
Messages: 11
Registered: March 2003
|
Junior Member |
|
|
Hi All,
Please can someone tell me which is the better option to use when the quote needs to be concatenated to a varchar2 value i.e.
In order to insert 'test' into a column exactly as yet see I used:
chr(39)||'test'||chr(39)
However, the DBA (OCP) says that is not a good way to do it and should be changed to:
''''||'test'||''''
Can someone please tell me if there's any logic to this?
Thanx
Sonic
|
|
|
|
|
|
|
|
| Re: Which is better - CHR(39) or '''' ? [message #167762 is a reply to message #167465] |
Sun, 16 April 2006 22:24  |
goodsadanand
Messages: 4
Registered: April 2006
|
Junior Member |
|
|
hi,
chr() will acheive same result but has poor readability...Not everyone can make out the result on seeing chr() in the script...
If a new developer has to carry forward an exising script,
it would have poor readability with chr()
|
|
|
|
| Re: Which is better - CHR(39) or '''' ? [message #167984 is a reply to message #167762] |
Tue, 18 April 2006 04:26 |
sonic
Messages: 11
Registered: March 2003
|
Junior Member |
|
|
Thats what the DBA said. However, its only a matter of figuring it out the first time and then you know it. Also, it gives jnr developers exposure to this function, which is quite handy e.g. what if you needed to control loops in terms of alphabets etc.
Thanx
|
|
|
|
| Re: Which is better - CHR(39) or '''' ? [message #168003 is a reply to message #167984] |
Tue, 18 April 2006 06:07 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Using a feature to give ' jnr developers exposure to this function' is nonsense. If you want junior developers to know about a function, either tell them or point them to some good reference about it.
In my opinion Maarten gave the only sensible way:
'''test'''
|
|
|
|
|
|
| Re: Which is better - CHR(39) or '''' ? [message #429487 is a reply to message #429437] |
Wed, 04 November 2009 03:15 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I actually prefer chr(39)||'test'||chr(39)
It lets you split out the strings from the extra quotes needed to get it to display.
@Gsanand - I doubt that you could ever spot the difference in performance that this would cause.
Thinking about it, there has to be a proces in Oracle that replaces the '' with a ' inside a strnig, so it's an interesting question as to which one is (infinitesimally) quicker.
|
|
|
|
|
|
| Re: Which is better - CHR(39) or '''' ? [message #430933 is a reply to message #167465] |
Fri, 13 November 2009 04:48 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
1. If 'better' is measured by readability (as seems to be the case here) then that is pretty much a subjective view and therefore the 'company policy' should be followed (it might not be an official policy, but you know what I mean)
2. I can't see there being a significant enough overhead from any of the mentioned techniques to warrant a choice based on speed.
3. if you are a junior developer and your DBA tells you that he want your code written in a specific way, i would generally suggest that you go along with that (unless the advice seems wrong in some way)
4. just to chuck the feline amongst the trafalgar square birdies i prefer a different option to those previously mentioned: using the quoted string delimiter:
insert into foo values (q'!'test3'!'); [Updated on: Fri, 13 November 2009 04:49] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Which is better - CHR(39) or '''' ? [message #430941 is a reply to message #430930] |
Fri, 13 November 2009 05:40 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote:Well..i think it's safe to assume a function such as chr() will cause an extra recursive call. Yes ?
You are barking up completely the wrong tree if you're trying to optimise SQL by whether or not it calls built in SQL functions. User defined functions can make a big difference to query performance. Built in functions are designed to be used this way.
The database does so much stuff behind the scenes that arguing that approach x consumes one more cpu cycle will never yield a noticable return as far as tuning goes.
I tested the time taken for 100,000,000 variable assignments (var := chr(39);, var := '''';, and var := c_chr; where c_chr is a defined constant) and the var := ''''; came out about 5% faster, which worked out as 0.4 seconds over 100 million operations.
Over 1,000,000 sql operations, the '''' approach came out at pretty much the same speed (-0.5% slower to 2% faster) - 0.05 seconds faster over 1,000,000 executions.
The potential for improvements in performance here is virtually nill. As soon as you have any other operations in the queries, these changes will disappear down into the noise.
|
|
|
|
| Re: Which is better - CHR(39) or '''' ? [message #431132 is a reply to message #430941] |
Sun, 15 November 2009 16:25 |
wakula
Messages: 150
Registered: February 2008
Location: Poland
|
Senior Member |
|
|
CODE INJECTIONS...
Seems like no one pointed that out (or I have overlooked it) but you should avoid such inserts. Imagine following terrible situation:CREATE TABLE usr_pwd ( usr VARCHAR2(10),pwd VARCHAR2(100));
To check if the user specified provided correct credentials unexperienced developer might use'SELECT 1 FROM usr_pwd WHERE usr='||chr(39)||$usr||chr(39)||' AND pwd='||chr(39)||$pwd||chr(39);
Then someone might provide a username with ' and ; in it... and you would authorize the user without password.
|
|
|
|
| Re: Which is better - CHR(39) or '''' ? [message #431185 is a reply to message #431132] |
Mon, 16 November 2009 01:31 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Although what you say is true, you cannot tell if this is the intended use for the question asked (unless I overlooked something in the thread).
It might be that this construct is used to generate some dynamic query string, or to concatenate a fieldvalue with a constant to create a new field value, etc, etc.
|
|
|
|
| Re: Which is better - CHR(39) or '''' ? [message #431202 is a reply to message #431185] |
Mon, 16 November 2009 03:28 |
wakula
Messages: 150
Registered: February 2008
Location: Poland
|
Senior Member |
|
|
Although I cannot tell what is the intend of the question - usually you can re-write your query to use pl/sql bound variables.
Although EXECUTE IMMEDIATE is the preferred oracle query execution syntax they allow (unnamed) bound variables there and DBMS_SQL allows named variables.
If you are using hard-coded strings in your queries then you should be aware that it is not secure.
|
|
|
|
| Re: Which is better - CHR(39) or '''' ? [message #431207 is a reply to message #431202] |
Mon, 16 November 2009 03:47 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Quote:If you are using hard-coded strings in your queries then you should be aware that it is not secure. Hmm not sure that that is what you actually meant to say there wakula.
Quote:Although EXECUTE IMMEDIATE is the preferred oracle query execution syntax they allow (unnamed) bound variables there and DBMS_SQL allows named variables.
What does this have to do with the question? Just to recap, the original question was asking what was trhe best method for insrting an apostrophe into a string to be inserted into a column in a table. There was nothing about using substitution variables, nor was there anything about PL/SQL.
|
|
|
|
| Re: Which is better - CHR(39) or '''' ? [message #431216 is a reply to message #431132] |
Mon, 16 November 2009 04:09 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Also, you can committhe same mistake using the doubled quote syntax:
'SELECT 1 FROM usr_pwd WHERE usr='''||$usr||''' AND pwd='''||$pwd||''';'
This error is independent of how you chose to quote delimit strings in dynamic SQL.
|
|
|
|
| Re: Which is better - CHR(39) or '''' ? [message #431219 is a reply to message #431202] |
Mon, 16 November 2009 04:12 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote:Although EXECUTE IMMEDIATE is the preferred oracle query execution syntax...
I have to take issuse with this statement.
NDS may well be the preferred (or at least simplest) way of executing dynamic SQL, but the preferred way of doing it is to avoid dynamic SQL entirely.
|
|
|
|
| Re: Which is better - CHR(39) or '''' ? [message #431222 is a reply to message #431207] |
Mon, 16 November 2009 04:13 |
wakula
Messages: 150
Registered: February 2008
Location: Poland
|
Senior Member |
|
|
pablolee wrote on Mon, 16 November 2009 10:47What does this have to do with the question? Just to recap, the original question was asking what was trhe best method for insrting an apostrophe into a string to be inserted into a column in a table. There was nothing about using substitution variables, nor was there anything about PL/SQL.
If you need to insert apostrophe into your SQL query then it is possibly non-secure thing. Instead you should use bound variables and then there would be probably no need to insert apostrophe.
|
|
|
|
| Re: Which is better - CHR(39) or '''' ? [message #431228 is a reply to message #431222] |
Mon, 16 November 2009 04:23 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Quote:If you need to insert apostrophe into your SQL query then it is possibly non-secure thing. Instead you should use bound variables and then there would be probably no need to insert apostrophe.
Please show me how
INSERT INTO x VALUES (1, 'abc', chr(39)||'xyz'||chr(39));
Is insecure. there is no SQL injection issue there. The issue may arise if one is using substitution variables to supply the text strings, but that was not mentioned in any way in the initial post (nor any subsequent posts prior to your post) In addition, NDS simply would NOT be the preferred solution here if incorporating the task into PL/SQL. One would use exactly the same method and if supplying the text values at runtime, simple, local PL/SQL variables would do the job perfectly well without incurring the risk of SQL injection.
|
|
|
|
| Re: Which is better - CHR(39) or '''' ? [message #431231 is a reply to message #431219] |
Mon, 16 November 2009 04:27 |
wakula
Messages: 150
Registered: February 2008
Location: Poland
|
Senior Member |
|
|
JRowbottom wrote on Mon, 16 November 2009 11:12
I have to take issuse with this statement.
NDS may well be the preferred (or at least simplest) way of executing dynamic SQL, but the preferred way of doing it is to avoid dynamic SQL entirely.
If I remember it correctly (sorry for not providing any links) then you are supposed to use pre-compiled SQL but sometime it is not enough. THen you need to use EXECUTE IMMEDIATE. You should avoid DBMS_SQL because it is a different style of coding than it is in general for PL/SQL. And then some things cannot be coded using only EXECUTE IMMEDIATE (or it might be easier to do them using DBMS_SQL) and this is why you have all the options still present in Oracle 11. In addition EXECUTE IMMEDIATE can be now executed using very long statements (>32k - although I have not tested that) which was available by using DBMS_SQL in Oracle 10.
Note that "some things cannot be coded using only EXECUTE IMMEDIATE"...
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
