How to handle a variable with an apostrophe

1

I have variable named $varfdetails. Let's say a user enters:
THERES NO WAY THIS SHOULDVE BEEN ALLOWED
The string is stored in $varfdetails and now I need to update the DB. I currently have the following statement that will update the DB IF no apostrophe is entered:
$sql = "UPDATE commonemails SET
county = '$varcounty',
city = '$varcity',
court = '$varcourt',
offer = '$varoffer',
details = '$vardetails'
WHERE id = ".$varid;

Now, lets say a user enters:
THERE'S NO WAY THIS SHOULD'VE BEEN ALLOWED
The above UPDATE statement doesn't work and I get a syntax error. How would I go about getting the statement to work since the user used apostrophes in the string that is stored in the variable $varfdetails

2

Use single quote as escape for single quotes. For example, the output of this replace can be copied and assigned to a variable.

SELECT  REPLACE ('THERE''S NO WAY THIS SHOULD''VE BEEN ALLOWED','''','''''')
3

Your code is vulnerable to injection. I recommend you look into how to use parameterization where you prepare a statement, bind parameters, then execute it. Doing this will keep your code from failing just because a user enters characters that are meaningful to SQL, like ', --, /*, */, etc.

2 Likes
4

JamesK
Thank you for your reply

I tried the following and received the syntax error again:

$vardetails = $_REQUEST['vardetails']; because the value is from a different page
Now $vardetails value is: THERE'S NO WAY THIS SHOULD'VE BEEN ALLOWED

Clicking the Submit button is supposed to update the DB using the following statement:

$sql = "UPDATE commonemails SET
county = '$varcounty',
city = '$varcity',
court = '$varcourt',
offer = '$varoffer',
details = REPLACE('$vardetails','''','''''')
WHERE id = ".$varid;

This is the error I received:
Error updating record: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Please find the attached letter. Please review it at your ea...' at line 8