Validating a Comments or Message textarea

[asdren_one]

I'm creating a straightforward contact form where I'm asking for:

Name [input]

Email [input]

Subject [input]

Message [textarea]

However, I'm having problems with the text I enter into the Message box

I'm noticing the following isssues:

a) apostrophes

If I enter an apostrophe I get a sql syntax error.  

    ex: That's the way it is.

This causes and error to occur in the SQL insert statement.

b) spaces are not conserved.

If I enter a message with 2-3 paragraphs of text. In the mysql database it seems to store as one long string of text and does not conserve the paragraph breaks

What am I missing? What functions am I overlooking that will sanitize the apostrophes and conserve paragraph breaks?
I realize the apostrophe bug can be quite serious as it leaves open possibility of a malicious SQL injection.

thanks in advance.

[Jochem van Dieten]

Always use cfqueryparam for all parameters in a query. It converts them to bind variables which will both prevent errors and protect against SQL injection attacks.

I think the line breaks are working as they should and the display is just ignoring them. Remember, in HTML you need a <br /> tag instead of a line break otherwise the display will ignore it. The MySQL client may be doing something similar.

[BKBK]

What am I missing? What functions am I overlooking that will sanitize the apostrophes and conserve paragraph breaks?

To add to what Jochem has suggested:

<cfqueryparam cfsqltype="cf_sql_varchar" value="#htmlcodeformat(form.message)#">

[Dan_Bracuk]

I recommend against formatting the value before you store it.  Save that for when you display it on a web page.  You never know when you'll want it displayed somewhere else.

[Adam Cameron.]

I recommend against formatting the value before you store it.  Save that for when you display it on a web page.  You never know when you'll want it displayed somewhere else.

Agreed.  Separation of view & storage 'n' all.

--

Adam