Encoding Basics

[HenryCan]

I'm finishing up my first few php programs. They are getting input from a user via an HTML form, validating that data at both the client and server sides, and then inserting the data from the form into a MySQL table.

 

It's actually working pretty well in most respects but I'm having a bit of a problem with apostrophes, otherwise known as single quotes. The forms can ask for a title of a book or film and when those titles contain apostrophes, such as Ender's Game or Logan's Run, the insert statement to the database breaks. I believe the apostrophe gets misinterpreted in the Insert statement as closing the apostrophe that preceeds the variable name. Therefore, if the title is Ender's Game, the '$title' gets messed up by having a single quote in the middle of the title. This is the actual insert statement from my code:

 

$insert = "INSERT INTO TopicProposals_Themes
(Date_Proposed, Proposer, Title, Discuss, Prepare, Comments)
VALUES ('$date_proposed', '$proposer', '$title', '$discuss', '$prepare', '$comments')";

$result = mysql_query($insert, $con);

if (!$result) {
 throw new Exception('Insert of Topic Proposal (Theme) into table failed. Please contact the webmaster. Error number: ' . mysql_errno($con) . '. Error message: ' . mysql_error($con));
}

 

So, what is the correct remedy for this situation? Should I simply change the apostrophes in the insert statement to be quotes ("") instead of (')? Or am I right in suspecting that I need to encode the values when I read them from the form, converting the apostrophes to &apost; and then write the encoded version to the database?

 

I've never had much to do with encoding and decoding and I'm still not clear on the difference between apostrophes and quotes in php so forgive my ignorance in knowing what the right solution is.

[SofWare]

See this page: http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php

[HenryCan]

Thanks, SofWare, I've amended my code as follows:

 

	   $insert = mysql_real_escape_string("INSERT INTO TopicProposals_Themes (Date_Proposed, Proposer, Title, Discuss, Prepare, Comments)
			   VALUES ('$date_proposed', '$proposer', '$title', '$discuss', '$prepare', '$comments')");

    echo "Insert statement: " . $insert . '<b/>';


    if (!$result) {
	    throw new Exception('Insert of Topic Proposal (Theme) into table failed. Please contact the webmaster. Error number: ' . mysql_errno($con) . '. Error message: ' . mysql_error($con));
    }

 

I'm still getting a syntax error, mysql error 1064, so there is apparently more wrong than just the unescaped apostrophes. I'll keep muddling away at it.

 

Thanks for helping me eliminate that as my problem.

[Jessica]

You use escape_string on each variable, not the whole query.

 

However, you should really look into using PDO instead.

[HenryCan]

Jessica, you're right. I've discovered PDO and written all my newer code to use it. I just have to go back and retrofit the old stuff with PDO.