Apostrophe and SQL problem

[JazzLeg]

Hi,
I am taking information from users via a text area, this data is then inserted into a database.

However if the user uses an apostrophe (') the Insert statement fails when it executes the SQL.

I would like to collect information with apostrophe's in it.

How can i get around this problem?

Thanks

 

[usesql]

You can replace the (') using

<%
form_item=request.form(&quot;form_item&quot
form_item=replace(form_item,&quot;'&quot;,&quot;&#39;&quot
%>

 

[JazzLeg]

Can the apostrophes be replaced with characters not found on the keyboard, so that when the information is selected from the database and displayed the apostrophes can be put back again?

 

[Muddmuse]

Sure. You might consider writing a fuction to replace the apostrophes just prior to saving to the database and then whenever you retrieve data from the database run though another function that reverses what you replaced on the way in. Here is a simplified version where &quot;xxx&quot; could be whatever you want.

Function cleanInput(str)
cleanInput = replace(str, &quot;'&quot;, &quot;xxx&quot
End Function

Function cleanOutput(str)
cleanOutput = replace(str, &quot;xxx&quot;, &quot;'&quot
End Function

 

[mww22]

You can also try this:

your_variable = Trim(Replace(your_variable,chr(39),&quot;’&quot)

I'm assuming you are displaying the data in a browser once you've pulled it out from the database.

By the way if it doesn't show up right in this message board the value that you replace the apostrophy with is: in quotes, an '&' followed by a '#' then the number '146' followed by a ';'
This message board will display the actual html value for apostropy as an actual apostrophy that is why I spelled it out

Hope this helps!

 

[entaroadun]

Argh!

Read up on SQL injection in Google. Dynamically generating SQL strings directly from user input fields is a MAJOR security risk. I can terminate your SQL statement with my single quote, then follow it with a SQL statement which your server will happily execute!

PLEASE use stored procedures. You can pass your data in as parameters and not worry about scrubbing it. If you have any questions about this, please ask.

That goes for everyone in this forum. Data access should be encapsulated in stored procedures or you are exposing a major risk. You are warned!

 

[Tarwn]

It is not major security risk so long as you 1) Limit the length of the data and 2) Replace all single quotes by either double single quotes or a special word like mentioned above.

In addition to the above posts, use &amp;amp; to display ampersands in your posts, they will change to just &amp; and sdisplay correctly, so to clarify the above post, you would be replacing with &amp;#146;

Also, if you want a replacement that will absolutely not be resolved I have found that using []'s and setting up tags like the forum markup often works well.

-Tarwn &quot;If you eat a live toad first thing in the morning, nothing worse will happen all day long.&quot; - California saying
&quot;To you or the toad&quot; - Niven's restatement of California saying
&quot;-well most of the time anyway...&quot; - programmers caveat to Niven's restatement of California saying
(The Wiz Biz - Ri

 

[entaroadun]

I believe that tagging special characters is a kluge at best. However, if you will be viewing your data in plain HTML, you will have to go through and scrub special characters anyways.

On the other hand, if you want your database to have a faithful copy of user input, use stored procedures. Then you just pass your data in as a parameter and the SP handles the rest. This is the easiest, purest way, as it involves no workarounds on either the ASP or DB side.

 

[Tarwn]

If you Replace the single quotes with double single quotes (for MS SQL Server) the database does not execute the enclosed string since the double single quotes are an escape for single quotes, but still stores the data with single single quotes. This is not a kludge or a hack. You cannot always resort to storing statments in a stored procedure because sometimes it is required that you be able to create the statement dynamically. I have an update statement that updates approx 30 fields in a table that does not allow nulls. If I wanted to use a stored procedure for this I would need to be able to refill all of the fields that had been passed as nulls with the original value so that the stored procedure would work. Instead I can easily create my update statement on the fly and only update fields that have valid data instead of creating work arounds to make a stored procedure work.

I am not trying to argue, merely point out that each way of doing things has it's positive and negative sides, and that deciding on the easier manner is dependant wholly on what you are trying to achieve.

-Tarwn &quot;If you eat a live toad first thing in the morning, nothing worse will happen all day long.&quot; - California saying
&quot;To you or the toad&quot; - Niven's restatement of California saying
&quot;-well most of the time anyway...&quot; - programmers caveat to Niven's restatement of California saying
(The Wiz Biz - Ri