[Zope-DB] newbie question getting newlines into database

Zope Admin zope@search-engine.nu
Sun, 10 Feb 2002 20:36:12 +1300


--------------070301040202070900020401
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

hi,

I'm pretty new to zope and python and am wondering what the easiest 
solution is for what i guess must be a pretty common problem.

I'm trying to get data from a <textarea> form field into a database 
(mysql via Z MySQL Database Connection) without loosing the \n new lines 
or possible converting them into <br>'s before storing into database.

When I include the follow code in my Z SQL Menthod I get the following 
troubles.  to highlight the relavant code I've included <----> before 
and after.

INSERT INTO iqp
(type, listing, author, catID1, catID2, catID3, rating, votes)
VALUES
(
 <dtml-sqlvar type type="string">,
<---->
 <dtml-sqlvar listing type="string">,
<---->
 <dtml-sqlvar author type="string">,
 <dtml-sqlvar catID1 type="int">,
 <dtml-sqlvar catID2 type="int">,
 <dtml-sqlvar catID3 type="int">,
 <dtml-sqlvar rating type="float">,
 <dtml-sqlvar votes type="int">
);

totally removes '\n' newlines so the formatted data is stored in the 
database as one long line.


INSERT INTO iqp
(type, listing, author, catID1, catID2, catID3, rating, votes)
VALUES
(
 <dtml-sqlvar type type="string">,

<---->
 <dtml-var listing  newline_to_br>,
<---->

 <dtml-sqlvar author type="string">,
 <dtml-sqlvar catID1 type="int">,
 <dtml-sqlvar catID2 type="int">,
 <dtml-sqlvar catID3 type="int">,
 <dtml-sqlvar rating type="float">,
 <dtml-sqlvar votes type="int">
);

ends up giving me an error...

Error Type: OperationalError
Error Value: (1064, "You have an error in your SQL syntax near '\n 
'r',\n 0,\n 0,\n 0,\n 5,\n 1\n)' at line 12")

Even if the <dtml-var...> code did work I would not be very happy about 
using it as it opens up security problems by allowing possible suspect 
SQL code through to my database unchecked.

I was thinking perhaps I will need to run the 'listings' variable 
through a python script and convert all '\n' to '<br>' before it is 
passed to the Z SQL Method.

I have spent about couple hours having a hunt around the web for a 
python script I can "recycle" into doing this for me by no luck yet.

Two questions;

1)  is my idea of processing the field with a script so i can get new 
line data from a <textarea> form through to a mysql 'text' field along 
the correct path?  Or is there something simpler that I have overlooked 
to do this without needing scripting.

2)  Assuming the script is the right thing to do does anyone have a 
script that already does this they can email me?



Thanks for anyone help on this wee problem of mine



Robert Anderson

code monkey
eCommerce Magic
Wellington
New Zealand

--------------070301040202070900020401
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<html>
<head>
</head>
<body>
hi,<br>
<br>
I'm pretty new to zope and python and am wondering what the easiest solution
is for what i guess must be a pretty common problem.<br>
<br>
I'm trying to get data from a &lt;textarea&gt; form field into a database
(mysql via Z MySQL Database Connection) without loosing the \n new lines
or possible converting them into &lt;br&gt;'s before storing into database.<br>
<br>
When I include the follow code in my Z SQL Menthod I get the following troubles.
&nbsp;to highlight the relavant code I've included &lt;----&gt; before and after.<br>
<br>
INSERT INTO iqp<br>
(type, listing, author, catID1, catID2, catID3, rating, votes)<br>
VALUES<br>
(<br>
&nbsp;&lt;dtml-sqlvar type type="string"&gt;, <br>
&lt;----&gt;<br>
&nbsp;&lt;dtml-sqlvar listing type="string"&gt;,<br>
&lt;----&gt;<br>
&nbsp;&lt;dtml-sqlvar author type="string"&gt;,<br>
&nbsp;&lt;dtml-sqlvar catID1 type="int"&gt;,<br>
&nbsp;&lt;dtml-sqlvar catID2 type="int"&gt;,<br>
&nbsp;&lt;dtml-sqlvar catID3 type="int"&gt;,<br>
&nbsp;&lt;dtml-sqlvar rating type="float"&gt;,<br>
&nbsp;&lt;dtml-sqlvar votes type="int"&gt;<br>
);<br>
<br>
totally removes '\n' newlines so the formatted data is stored in the database
as one long line.<br>
<br>
<br>
INSERT INTO iqp<br>
(type, listing, author, catID1, catID2, catID3, rating, votes)<br>
VALUES<br>
(<br>
&nbsp;&lt;dtml-sqlvar type type="string"&gt;, <br>
<br>
&lt;----&gt;<br>
&nbsp;&lt;dtml-var listing&nbsp; newline_to_br&gt;,<br>
&lt;----&gt;<br>
<br>
&nbsp;&lt;dtml-sqlvar author type="string"&gt;,<br>
&nbsp;&lt;dtml-sqlvar catID1 type="int"&gt;,<br>
&nbsp;&lt;dtml-sqlvar catID2 type="int"&gt;,<br>
&nbsp;&lt;dtml-sqlvar catID3 type="int"&gt;,<br>
&nbsp;&lt;dtml-sqlvar rating type="float"&gt;,<br>
&nbsp;&lt;dtml-sqlvar votes type="int"&gt;<br>
);<br>
<br>
ends up giving me an error...<br>
<br>
<strong>Error Type: OperationalError</strong><br>
<strong>Error Value: (1064, "You have an error in your SQL syntax near '\n
'r',\n 0,\n 0,\n 0,\n 5,\n 1\n)' at line 12")</strong><br>
<br>
Even if the &lt;dtml-var...&gt; code did work I would not be very happy about
using it as it opens up security problems by allowing possible suspect SQL
code through to my database unchecked.<br>
<br>
I was thinking perhaps I will need to run the 'listings' variable through
a python script and convert all '\n' to '&lt;br&gt;' before it is passed
to the Z SQL Method.<br>
<br>
I have spent about couple hours having a hunt around the web for a python
script I can "recycle" into doing this for me by no luck yet.<br>
<br>
Two questions;<br>
<br>
1) &nbsp;is my idea of processing the field with a script so i can get new line
data from a &lt;textarea&gt; form through to a mysql 'text' field along the
correct path? &nbsp;Or is there something simpler that I have overlooked to do
this without needing scripting.<br>
<br>
2) &nbsp;Assuming the script is the right thing to do does anyone have a script
that already does this they can email me?<br>
<br>
<br>
<br>
Thanks for anyone help on this wee problem of mine<br>
<br>
<br>
<br>
Robert Anderson<br>
<br>
code monkey<br>
eCommerce Magic<br>
Wellington <br>
New Zealand<br>
</body>
</html>

--------------070301040202070900020401--