Home » Fusion Middleware & Colab Suite » Weblogic & Application Server » problems with cr/lf in string in ASP
| problems with cr/lf in string in ASP [message #128178] |
Fri, 15 July 2005 10:13  |
gdeconto
Messages: 41
Registered: June 2005
Location: Vancouver, British Columb...
|
Member |
|
|
I have found something odd with the orawin924 odbc driver when using it in an ASP page. I am unable to pass strings to a stored procedure if the string contains a carriage return/line feed character.
I get the following error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Oracle][ODBC]Syntax error or access violation.
which I was able to track the problem down to the cr/lf combo. if the strings I pass in have no cr/lf, then the save works. If the strings I pass in have one or more cr/lf, then the save fails and gives me the above error.
I have not been able to figure out an easy way to escape out the cr/lf combos. I also am not able to use alternative means of sending the code to Oracle (ie creation of oledb commands, parameters, etc such) since there is extensive code that exists with this methodology and I cant change it.
Here is a test example of what I have done. Please note that I know that the test does not take into account things like strings with single quotes, etc. (this is just a test example).
Hopefully someone can help me figure out a way to save the crlf in strings.
1) create a simple test table:
CREATE TABLE TESTTABLE
(
EMPNO NUMBER,
DESCR VARCHAR2(512 BYTE)
OTHER VARCHAR2(10 BYTE)
);
2) create a simple stored procedure to populate the table:
PROCEDURE "SCOTT".PUTEMPINFO(
p_empno IN NUMBER DEFAULT NULL,
p_descr IN VARCHAR2 DEFAULT NULL,
p_other IN VARCHAR2 DEFAULT NULL)
IS
m_rowcount NUMBER := 0;
BEGIN
UPDATE testtable
SET descr = p_descr, other = p_other
WHERE empno = p_empno;
m_rowcount := SQL%ROWCOUNT;
IF m_rowcount = 0
THEN
INSERT INTO testtable
(empno, descr, other)
VALUES (p_empno, p_descr, p_other);
END IF;
COMMIT;
END;
3) create simple ASP page to update the table:
<!--#include virtual="/include/adovbs.asp"-->
<%
if len(request.form("empno")) > 0 then
connStr = "DSN=ORACLE9I; USER ID=scott; PASSWORD=tiger;"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open connStr
strsql = "{ call putempinfo (" & request.form("empno") & ",'" & server.htmlencode(request.form("descr")) & "','" & request.form("other") & "') }"
set rs = objConn.Execute(strSQL)
end if
%>
<html>
<head>
<title>Untitled</title>
</head>
<body>
<form action="oracletest.asp" method="post">
<table>
<tr>
<td>Empno:</td>
<td><input type="text" name="empno" size="4" maxlength="4"></td>
</tr>
<tr>
<td>Descr:</td>
<td><textarea cols="50" rows="3" name="descr"></textarea></td>
</tr>
<tr>
<td>Other:</td>
<td><input type="text" name="other" size="10" maxlength="10"></td>
</tr>
<tr>
<td colspan="2"><input type="submit" value="Save Info"></td>
</tr>
</table>
</form>
</body>
</html>
4) to test, enter any value for empno and other (ie 1234 and asdf) and enter two separate lines (ie hit Enter between the lines) and submit the form.
any help appreciated.
|
|
|
|
| Re: problems with cr/lf in string in ASP [message #128181 is a reply to message #128178] |
Fri, 15 July 2005 10:27  |
kmmfoo
Messages: 38
Registered: June 2005
Location: massachusetts
|
Member |
|
|
The most typical approach to solving this problem is to encode the crlf on the way into the database, and decode it on the way out.
For example, consider an approach where you translate "crlf" into the standard c-ish "\n" (backslash n). On your way in, convert any vbCrlf strings like so...
- - - begin snip - - -
Dim encodedDescr
encodedDescr = Replace(request.form("descr"), vbCrlf, "\n")
encodedDescr = server.htmlencode(encodedDescr)
strsql = "{ call putempinfo (" & request.form("empno") & _
",'" & encodedDescr & _
"','" & request.form("other") & "') }"
- - - end snip - - -
Then when you retrieve the results from the database and go to display them on the screen do the reverse... assuming that you've retrieved the value into a recordset, and its field name is "descr":
- - - begin snip - - -
Response.Write Replace(rs("descr"), "\n", vbCrlf)
- - - end snip - - -
Of course as we know, in general crlf has no impact on HTML output... but whatever.
Which means that you *might* just want to change the "wrap" parameter on your <textarea> tag... avoiding all of this in the first place.
HTH...
|
|
|
|
| Re: problems with cr/lf in string in ASP [message #128189 is a reply to message #128181] |
Fri, 15 July 2005 10:56 |
gdeconto
Messages: 41
Registered: June 2005
Location: Vancouver, British Columb...
|
Member |
|
|
thx for the response kmmfoo.
1) do you happen to know if this behavior in the odbc driver is normal and expected (ie not a bug)?? I havent had this problem in mssql (for example) and thought that the problem might be related to some sort of setting somewhere at either the odbc end or at the oracle server end or ???
2) I had looked at using some sort of substitution but was reluctant to do so; there is a lot of existing code and am not sure I can (ie not allowed to) change it all.
3) can you explain your last comment re changing the wrap parameter??
|
|
|
|
| Re: problems with cr/lf in string in ASP [message #128212 is a reply to message #128189] |
Fri, 15 July 2005 14:01 |
kmmfoo
Messages: 38
Registered: June 2005
Location: massachusetts
|
Member |
|
|
Hi --
Here's some background on why the original problem is "working as designed" rather than a bug. Look at the line of your code that creates the SQL call... it looks like this:
strsql = "{ call putempinfo (" & request.form("empno") & ",'" & server.htmlencode(request.form("descr")) & "','" & request.form("other") & "') }"
Okay, now imagine you replace the request parameters with some typical pieces of client input. Assume:
Request("empno") is "123"
Request("other") is "whatever"
Request("descr") contains text and newline characters,
for example
"this[crlf]is[crlf]a[crlf]test"
Okay, fine. Now construct the contents of variable strsql, it looks like
{ call putempinfo (123,'this
is
a
test','whatever') }
Is this a well-formed SQL query? To find out, try to execute it inside SQLplus... but I'll give you a hint, it will fail.
Next your code takes that string and does an
objConn.Execute(strSQL)
Well, think about what happens when you execute a malformed SQL query: You get a syntax error. Probably almost identical to the error you'd get when you executed the query inside SQLplus.
It has nothing to do with the driver, it has everything to do with a malformed SQL command.
Okay... and the answer is? Well, one answer would be to strip out and replace the [crlf] characters in the input, the way I first suggested. I'm not 100% sure what you expect to happen with these [crlf] strings when you retrieve them... do you want to be able to preserve them, and ship them back to your users? If so, are you doing all of your work in HTML? In that case, perhaps you could think about replacing them with "<br>" tags... but in any case you _have_ to do something with it. Unless of course you don't care about preserving the [crlf] characters: do you?
What I mean is, suppose your users typed in
this
is
a
test
How unhappy would they be if you simply returned "this is a test" when you retrieved the data from the database in the end? If that's okay, then you can just convert [crlf] into " " (a blank) on the input side, and nobody's the wiser. Or, as I suggested, you can monkey with the "wrap" property of your <textarea> tag. Check your browser specs, but the two major accepted values are
soft: Text is displayed with word wrapping and submitted without carriage returns and line feeds.
hard: Text is displayed with wordwrapping and submitted with soft returns and line feeds.
HTH...
|
|
|
|
| Re: problems with cr/lf in string in ASP [message #128226 is a reply to message #128212] |
Fri, 15 July 2005 17:37 |
gdeconto
Messages: 41
Registered: June 2005
Location: Vancouver, British Columb...
|
Member |
|
|
thats what I figured (that the sql command is sent to oracle in the same way that commands are sent via sql*plus, and has the same )(&#!@%$$ limitations ... sigh)
like quite a few folks on these forums, I am coming in from an mssql background, where this kind of limitation does not exist. just find it odd/frustrating that you have to terminate a command line with ";" or "/" to indicate the end of a command but the interpreter still requires that you put the whole command on one single, solitary line. if the guy who designed this in had worked for me, I'd have demoted him to janitor.
at any rate, the cr/lf are required in the text so soft returns wont do.
I can escape out the cr/lf (ie using "\n" in C, C++ and C#) but then I have to worry about instances where users might type this sequence in (ie http:\\newpage.mydomain.com). I then have to escape out the escape char in some instances (ie using "\\" to indicte a single non-command "\") and so on).
definitely a pain the rear.
I would imagine this comes up now and then. did anyone come up with a sequence of code to handle this or is it back to reinventing the wheel for me?? probably the latter.
appreciate your responses. many thanks.
[Updated on: Fri, 15 July 2005 17:38] Report message to a moderator |
|
|
|
| Re: problems with cr/lf in string in ASP [message #128424 is a reply to message #128226] |
Mon, 18 July 2005 08:13 |
kmmfoo
Messages: 38
Registered: June 2005
Location: massachusetts
|
Member |
|
|
Hi --
I'm a little bit surprised to hear you say that you didn't have this sort of problem back in your SQL Server days -- in general anything you ship to a "Connection.Execute( )" has this same sort of crlf problem. Of course in SQL Server you're much more likely to run into a system that's been built using the command parameter model or the recordset field model, and you're right -- that problem doesn't happen when you set the values of parameters or the values of fields.
I'm not familiar with any "commonly-accepted best practices" for how this should be handled since, as I mentioned, what you do depends entirely on whether you want your crlfs preserved, or converted to whitepace, or converted to <br> tags... etcetera. But the typical approach in classic VB6 and/or ASP is to create a callable function that you can wrap your database fields in -- I typically call it something like "PrepareForSQL( )" -- it does whatever conversions you need, and returns the converted string, suitable for use in your SQL command.
BTW, while you're doing this keep in mind that it's not just crlfs that could kill your input. Consider as well what would happen if your visitors typed an apostrophe into your query. At best a syntax error borne of malformed syntax -- at worst a SQL injection attack that could compromise your server.
So hope this helps.
|
|
|
|
| Re: problems with cr/lf in string in ASP [message #128437 is a reply to message #128424] |
Mon, 18 July 2005 09:30 |
gdeconto
Messages: 41
Registered: June 2005
Location: Vancouver, British Columb...
|
Member |
|
|
I have similar functions that take incoming form fields and massage them for sending to SQL/Oracle.
For MSSQL, the only real concern was to escape out the apostrophe in strings (since it is a string delimiter). This was/is, of course, important in preventing sql insertion attacks.
Aside from the CR/LFs, is there anything else I need to concern myself with Oracle?? ie ":" or "?" or ?????
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 03:35:34 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
