We help IT Professionals succeed at work.

Escape single quote before insert into mssql

BHUC
BHUC asked
on
I don't know why I am struggling with this so bad, but I have a simple form field (text area) that gets inserted into a database. After switching to mssql instead of mysqli I can't figure out how to escape the single quotes and get the insert or update to work.

if I have this form field
Comments: <textarea name="RACOMMENTS" cols="100" rows="10"  /></textarea>

and this update:
"update table set RACOMMENTS='" . $_POST['RACOMMENTS'] . "' ";
$result-sqlsrv_query($conn,$sql);

How do I escape that field to allow a word like don't or can't or sister's
Comment
Watch Question

View Solution Only

Technology and Business Process Advisor
Most Valuable Expert 2013
Commented:
Replace the single with TWO singles.

"update table set RACOMMENTS='" . str_replace("'", "''", $_POST['RACOMMENTS']) . "' ";
BHUC

Author

Commented:
Thank you!!!
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
You can use \(backslash) to escape single quotes.

Read more from - http://us3.php.net/mysql-real-escape-string
NerdsOfTechTechnology Scientist

Commented:
If you were previously allowing single quotes unescaped, that means you might have other queries that can potentially be at risk.

I recommend using prepared SQL statements; not only will you avoid having to escape strings but you avoid the possibility of SQL injection attacks as well.

Otherwise escaping and sanitizing (filtering) the inputs, or in some cases the outputs (namely when the records were recorded without filtration), is recommended.