Apostrophe Errors

[USlegacies]

Below is a sample code that I need help on.

I am trying to allow certain elderly non-computer savvy individuals to use a cell phone or computer to fill out an online form.

My problem is when they try to insert an apostrophe into the form, it displays an error.

As an extremely elderly individual myself with no formal computer training in php or mysql, I get lost very easy when trying to sort through so many outdated replies in search engines. Therefore I am hoping someone can give me a simple example of code that will work with the particular version of php and mysql that I am using.

I am using multiple html, css and php pages for the form and db connection.

The php version I am using is 7.4 and mysql 8.0.31

I am trying to avoid using a higher version of php for now, due to having multiple programs running on the server that could cause problems if I went to php 8.0

Below is an example of the code I have been using and it works great, except for the apostrophe errors.

So if anyone can give me an example of the code I need to use, I would be greatly appreciative.

I will also add that anyone filling this form out, will need to be a registered member of a private website, so that adds a bit of security to it.

There are around 60 different fields in the database, so whatever code it used may have to be repeated often.

help_needed.txt

[Phi11W]

I would guess that you're building your SQL in a string variable, complete with values entered by the user, and then trying to execute that string against the database. 
If that's the case, you're falling foul of a classic SQL Injection Attack. 

$sql = "select * from table1 where username = '$uName'"

But here the User is entering something like O'Brien, so your SQL string looks like this ... 

$sql = "select * from table1 where username = 'O'Brien'"
                                                ^
                                                 Boom!

... and blows up! 

 

Read up about prepared statements (a.k.a. Parameterised Queries). 

Obligatory XKCD Reference: Little Bobby Tables. 

 

Regards,
   Phill  W.

[USlegacies]

Thank you for responding.

I am not sure exactly what you are suggesting, however the form we are setting up is NOT for the public to view, search or edit. Rather it is designed for individuals to "Fill  in the form" with data that only office staff can read. The only person that will have access to edit any data will be the db designer.

So, basically, all we are trying to do is escape or find a way to deal with the errors we are receiving when someone uses an apostrophe when they enter in their name or as part of a text field, for the php version we are using.

Edited November 30, 2022 by USlegacies

[Barand]

A prepared query is the best way to accomplish that.

MYSQLI

$inputname = "O'Brien";
$res = $mysqli->prepare("SELECT whatever from table1 where lastname = ? ";
$res->bind->param("s", $inputname);
$res->execute();

PDO

$inputname = "O'Brien";;
$res = $pdo->prepare("SELECT whatever from table1 where lastname = ? ";
$res->execute( [$inputname] );

 

[mac_gyver]

due to things like cross site scripting and phishing attacks, even if this form and form processing code is only accessible to logged in users, the data submitted can be anything, cannot be trusted, and must be used securely in every context (sql, html, mail header, file operation.) a lot of web sites have been taken over due to injected code in values that were 'only being viewed by staff members'.

the posted code example is - insecure (the current problem), provides a poor user experience (when there are validation errors, which the code doesn't even perform, by putting the form and form processing code on separate pages, requires the user to keep reentering data over and over), is filled with copying of variables to other variables for nothing, and has error handling for the database statement that would only confuse the visitor.

prepared queries are the simplest, fool-proof way of preventing any sql special characters in a value, a ' in this case, from breaking the sql query syntax. however, the mysqli database extension is overly complicated and inconsistent when dealing with prepared queries. you would want to switch to the much simpler and more modern PDO database extension.

the form processing code and form should be on the same page. this will reduce the amount of code that must be written, allow any user/validation errors to displayed with the form, and allow previously entered data values to repopulate the form fields so that the user doesn't need to keep reentering data upon an error.

you should use exceptions for database statement error handling and only catch and handle the exception in your code for user recoverable errors, such as inserting/updating duplicate or out of range user submitted values. in all other cases, simply let php catch and handle the exception,  where php will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors) requiring no logic in your code at all.

3 hours ago, USlegacies said:

There are around 60 different fields in the database

for more than about 2-3 form fields, you should use a data-driven design, where you have an array that defines the expected fields, validation steps, and processing, that you would simply loop over using general purpose code, rather then writing out, testing, debugging, and maintaining a bunch of bespoke code for every possible field.