PHP inserting Apostrophes where it shouldn't
- by Jack W-H
Hi folks
Not too sure what's going on here as this doesn't seem like standard practise to me. But basically I have a basic database thingy going on that lets users submit code snippets. They can provide up to 5 tags for their submission.
Now I'm still learning so please forgive me if this is obvious!
Here's the PHP script that makes it all work (note there may be some CodeIgniter specific functions in there):
function submitform()
{
$this->load->helper(array('form', 'url'));
$this->load->library('form_validation');
$this->load->database();
$this->form_validation->set_error_delimiters('<p style="color:#FF0000;">', '</p>');
$this->form_validation->set_rules('title', 'Title', 'trim|required|min_length[5]|max_length[255]|xss_clean');
$this->form_validation->set_rules('summary', 'Summary', 'trim|required|min_length[5]|max_length[255]|xss_clean');
$this->form_validation->set_rules('bbcode', 'Code', 'required|min_length[5]'); // No XSS clean (or <script> tags etc. are gone)
$this->form_validation->set_rules('tags', 'Tags', 'trim|xss_clean|required|max_length[254]');
if ($this->form_validation->run() == FALSE)
{
// Do some stuff if it fails
}
else
{
// User's input values
$title = $this->db->escape(set_value('title'));
$summary = $this->db->escape(set_value('summary'));
$code = $this->db->escape(set_value('bbcode'));
$tags = $this->db->escape(set_value('tags'));
// Stop things like <script> tags working
$codesanitised = htmlspecialchars($code);
// Other values to be entered
$author = $this->tank_auth->get_user_id();
$bi1 = "";
$bi2 = "";
// This long messy bit basically sees which browsers the code is compatible with.
if (isset($_POST['IE6'])) {$bi1 .= "IE6, "; $bi2 .= "1, ";} else {$bi1 .= "IE6, "; $bi2 .= "NULL, ";}
if (isset($_POST['IE7'])) {$bi1 .= "IE7, "; $bi2 .= "1, ";} else {$bi1 .= "IE7, "; $bi2 .= "NULL, ";}
if (isset($_POST['IE8'])) {$bi1 .= "IE8, "; $bi2 .= "1, ";} else {$bi1 .= "IE8, "; $bi2 .= "NULL, ";}
if (isset($_POST['FF2'])) {$bi1 .= "FF2, "; $bi2 .= "1, ";} else {$bi1 .= "FF2, "; $bi2 .= "NULL, ";}
if (isset($_POST['FF3'])) {$bi1 .= "FF3, "; $bi2 .= "1, ";} else {$bi1 .= "FF3, "; $bi2 .= "NULL, ";}
if (isset($_POST['SA3'])) {$bi1 .= "SA3, "; $bi2 .= "1, ";} else {$bi1 .= "SA3, "; $bi2 .= "NULL, ";}
if (isset($_POST['SA4'])) {$bi1 .= "SA4, "; $bi2 .= "1, ";} else {$bi1 .= "SA4, "; $bi2 .= "NULL, ";}
if (isset($_POST['CHR'])) {$bi1 .= "CHR, "; $bi2 .= "1, ";} else {$bi1 .= "CHR, "; $bi2 .= "NULL, ";}
if (isset($_POST['OPE'])) {$bi1 .= "OPE, "; $bi2 .= "1, ";} else {$bi1 .= "OPE, "; $bi2 .= "NULL, ";}
if (isset($_POST['OTH'])) {$bi1 .= "OTH, "; $bi2 .= "1, ";} else {$bi1 .= "OTH, "; $bi2 .= "NULL, ";}
// $b1 is $bi1 without the last two characters (, ) which would cause a query error
$b1 = substr($bi1, 0, -2);
$b2 = substr($bi2, 0, -2);
// :::::::::::THIS IS WHERE THE IMPORTANT STUFF IS, STACKOVERFLOW READERS::::::::::
// Split up all the words in $tags into individual variables - each tag is seperated with a space
$pieces = explode(" ", $tags);
// Usage:
// echo $pieces[0]; // piece1 etc
$ti1 = "";
$ti2 = "";
// Now we'll do similar to what we did with the compatible browsers to generate a bit of a query string
if ($pieces[0]!=NULL) {$ti1 .= "tag1, "; $ti2 .= "$pieces[0], ";} else {$ti1 .= "tag1, "; $ti2 .= "NULL, ";}
if ($pieces[1]!=NULL) {$ti1 .= "tag2, "; $ti2 .= "$pieces[1], ";} else {$ti1 .= "tag2, "; $ti2 .= "NULL, ";}
if ($pieces[2]!=NULL) {$ti1 .= "tag3, "; $ti2 .= "$pieces[2], ";} else {$ti1 .= "tag3, "; $ti2 .= "NULL, ";}
if ($pieces[3]!=NULL) {$ti1 .= "tag4, "; $ti2 .= "$pieces[3], ";} else {$ti1 .= "tag4, "; $ti2 .= "NULL, ";}
if ($pieces[4]!=NULL) {$ti1 .= "tag5, "; $ti2 .= "$pieces[4], ";} else {$ti1 .= "tag5, "; $ti2 .= "NULL, ";}
$t1 = substr($ti1, 0, -2);
$t2 = substr($ti2, 0, -2);
$sql = "INSERT INTO code (id, title, author, summary, code, date, $t1, $b1) VALUES ('', $title, $author, $summary, $codesanitised, NOW(), $t2, $b2)";
$this->db->query($sql);
$this->load->view('subviews/template/headerview');
$this->load->view('subviews/template/menuview');
$this->load->view('subviews/template/sidebar');
$this->load->view('thanksforsubmission');
$this->load->view('subviews/template/footerview');
}
}
Sorry about that boring drivel of code there. I realise I probably have a few bad practises in there - please point them out if so.
This is what the outputted query looks like (it results in an error and isn't queried at all):
A Database Error Occurred
Error Number: 1136
Column count doesn't match value count at row 1
INSERT INTO code (id, title, author, summary, code, date, tag1, tag2, tag3, tag4, tag5, IE6, IE7, IE8, FF2, FF3, SA3, SA4, CHR, OPE, OTH) VALUES ('', 'test2', 1, 'test2', 'test2 ', NOW(), 'test2, test2, test2, test2, test2', NULL, NULL, 1, 1, 1, 1, 1, 1, 1, NULL)
You'll see at the bit after NOW(), 'test2, test2, test2, test2, test2' - I never asked it to put all that in apostrophes. Did I?
What I could do is put each of those lines like this:
if ($pieces[0]!=NULL) {$ti1 .= "tag1, "; $ti2 .= "'$pieces[0]', ";} else {$ti1 .= "tag1, "; $ti2 .= "NULL, ";}
With single quotes around $pieces[0] etc. - but then my problem is that this kinda fails when the user only enters 4 tags, or 3, or whatever.
Sorry if that's the worst phrased question in history, I tried, but my brain has turned to mush.
Thanks for your help!
Jack
