Contents of text box causing PHP Query Problems

kev_akas

Hi,

I've searched about on this forum for the past hour and have found lots of responses about this, but have to apologise for not getting my head around it!

I'm writing an appraisal system for the company I work for, and have been coding it for (wait for it) the past 11months. Checking recently, the system consists of over 466 pages, and from coding from scratch, weighs a nice 3.6MB.

It's written in PHP5, with HTML etc... and the only massive difference is that I'm using a Microsoft SQL backend, instead of MySQL.

The system is in the final stages, and in testing this week, the first thing someone tried to do on the update section was to enter an apostrophe, which completely cattled the submit code, they put 'logic' in which was highlighted in the error.

I've looked about, and saw there is the mysql work around, as well as a couple of others but I can't quite get my head around it.

Here's a quick look at how it works.

Edit page has text boxes on.
1 of the text boxes is called appAttendanceComment, it is pre-populated with the database entry for that field, so users can edit it. eg;

<textarea name="appAttendanceComment" cols="70" rows="4" id="appAttendanceComment"><? print $Row['appAttendanceComment']; ?></textarea>

The edit page posts to a php update form, and is collected like this;

@$appAttendanceComment = addslashes($_POST['appAttendanceComment']);

When then goes to the query;

@Query = 
"UPDATE dbo.appraisal 
SET appAttendanceComment = '$appAttendanceComment', (bla bla bla) WHERE appID = '$appID'";

The code works a treat when the boxes don't have apostrophes in them, but bombs out the second they are in, also, if the box already includes a \ or " it keeps adding slashes to it everytime it is updated.

My head is (just a little) blagged with it and I'd love it if you can help with some wise words to help get the system out and about and the manager of the project off my back!

laserlight

Just to check: is magic_quotes_gpc set to On in php.ini? If so, set it to Off and restart the web server. If not, then you should check with [man]get_magic_quotes_gpc/man and use [man]stripslashes/man before you work with the incoming variable (e.g., to print it to the browser, or to use it in an SQL statement).

Next, is addslashes() even the correct way to escape single quotes? For some database engines, doubling single quotes is the correct way to escape them. Check the PHP manual on your database extension for the correct escaping mechanism (why are you not using prepared statements?), or if necessary, the Microsoft SQL documentation.

Now, this might not be a problem right now, but to avoid malicious client side script injection, you should use [man]htmlspecialchars/man when printing to the browser, e.g.,

<textarea name="appAttendanceComment" cols="70" rows="4" id="appAttendanceComment"><?php print htmlspecialchars($Row['appAttendanceComment']); ?></textarea>

kev_akas

Hi,

I checked the magic quotes (should of said in the first post really)
PHPInfo results;
magic_quotes_gpc Off
magic_quotes_runtime Off
magic_quotes_sybase Off

This is on PHP 5.2.3, running on Apache 2.

I looked up the manual on MS SQL - there's very little functions in it, just the basic stuff really. I don't think it's a very popularly used extension - if the company hadn't of explicitly stated they wanted it in Microsoft SQL I'd of used MySQL.

The page is inputting about 50 text boxes so I'm not sure of the quickest replacement for the;

@$appAttendanceScore = addslashes($_POST['appAttendanceScore']);
"UPDATE dbo.appraisal 
SET appAttendanceScore = '$appAttendanceScore' WHERE appID = '$appID'";

What would be your advice in writing the post bit getting the data from the form?
I don't have to write 3 or 4 lines of code per _POST do I?