Friday, April 27, 2012

SQL Insertion

Earlier this Spring I build an online login section for the CESAC website, so that future webmasters wouldn't have to touch the code to just put in a quick announcement update.

It worked well, until yesterday I tried putting in an update, and nothing happened. I got no PHP error, instead the PHP went through the Insertion and then passed over to a success page, but didn't insert.

Given that yesterday evening I turned in my final project of the semester, this afternoon I had time to sit down and go through the code line by line (not as bad as it seemed, the submit page was only 20ish lines). It took me a good two hours, but I finally figured out the cause. Whenever a string that I was inputting contained an apostrophe, it would screw up the SQL insertion. It wouldn't cause a problem with the PHP, which is why I wasn't getting an error.

I was inserting using variables, and used apostrophes to contain each variable, ex: ... '$month', '$date' ... So whenever the string had an apostrophe, it ended the variable, or so it thought. And ended up really messing up the statement.

Turns out SQL has a quick fix for this. Placing a backslash ( "\" ) in front of an apostrophe tells the SQL to treat it as a character, instead of a limiter. So the solution was simple really. I had to use str_replace to replace all apostrophes with double apostrophes. $content = str_replace("'","\'",$content);


No comments:

Post a Comment