...

View Full Version : An apostophe



rowantrimmer
02-23-2010, 03:47 PM
Hello,

I have a form and when an apostophe is entered into the field that field does not get wriien away into the php / mysql table?

Is it that the apostophe is being seen as a terminator to the Insert or Update function?

Is there anyway around this?

An example of my update line is as follows... where for instance if an apostophe is type into the title field it doesn't get updated

$recipe_updatesql = "UPDATE recipe SET title = '".$_POST['title']."', description = '".$_POST['description']."', type = '".$_POST['type']."', cuisine = '".$_POST['cuisine']."', serves = '".$_POST['serves']."', prep_mins = '".$_POST['prep_mins']."', prep_hours = '".$_POST['prep_hours']."', cook_mins = '".$_POST['cook_mins']."', cook_hours = '".$_POST['cook_hours']."', method = '".$_POST['method']."', tips = '".$_POST['tips']."', photo='".$photo."', story = '".$_POST['story']."', occasion = '".$_POST['occasion']."', need1=$need1, need2=$need2, need3=$need3, need4=$need4, need5=$need5, need6=$need6, need7=$need7, need8=$need8, need9=$need9, need10=$need10 where id='$id'";
$recipe_update = mysql_query($recipe_updatesql);

bacterozoid
02-23-2010, 03:50 PM
Yep, and any user could easily perform a SQL Injection Attack on your database. Always always always sanitize data before it goes into your database:

http://php.net/manual/en/function.mysql-real-escape-string.php

Shauny_B
02-23-2010, 03:57 PM
Hello,
Take a look at this:




$recipe_updatesql = "UPDATE recipe SET title = '".mysql_real_escape_string($_POST['title'])."', description = '".mysql_real_escape_string($_POST['description'])."', type = '".mysql_real_escape_string($_POST['type'])."', cuisine = '".mysql_real_escape_string($_POST['cuisine'])."', serves = '".mysql_real_escape_string($_POST['serves'])."', prep_mins = '".mysql_real_escape_string($_POST['prep_mins'])."', prep_hours = '".mysql_real_escape_string($_POST['prep_hours'])."', cook_mins = '".mysql_real_escape_string($_POST['cook_mins'])."', cook_hours = '".mysql_real_escape_string($_POST['cook_hours'])."', method = '".mysql_real_escape_string($_POST['method'])."', tips = '".mysql_real_escape_string($_POST['tips'])."', photo='".mysql_real_escape_string($photo)."', story = '".mysql_real_escape_string($_POST['story'])."', occasion = '".mysql_real_escape_string($_POST['occasion'])."', need1='". mysql_real_escape_string($need1) ....


You'll also need to be sure that the other $need variables are both inside '' (if string, and that they're escaped. If they're integer values use the function intval($variable) as then you're certain a number will be returned.

Take a look at the below, and see what I've done with the above:


need1=$need1, need2=$need2, need3=$need3, need4=$need4, need5=$need5, need6=$need6, need7=$need7, need8=$need8, need9=$need9, need10=$need10 where id='$id'";


If you escape and make sure the right (escaped) data formats are correctly within the query then you'll minimize SQL injections.

Happy coding,
Shaun

rowantrimmer
02-23-2010, 03:58 PM
Hello,

Thanks for that. Sorry I am very new to all this so do I need to do anything to POST['title'] when I write it away and also when I would read the table to display it?

rowantrimmer
02-23-2010, 04:01 PM
Hello Shauny_B,

I take it that I do exactly the same thing for an Insert but what about when I read the table and display the contents? Will the apostophe show up?

Shauny_B
02-23-2010, 06:11 PM
Hello,
Yeah, the ' basically means string for the MySQL query, wrapping variables inside the '' lets the query know string is inside it, but always make sure that you escape values coming direct from $_POST or especially $_GET with:



mysql_real_escape_string(); # if you're using the MySQL functions (like you're doing in this exampe

or

mysqli_real_escape_string(); # only if you're using the MySQLI instance


What a SQL injection is, is basically if you have:



$sql = "SELECT * FROM tbl_name WHERE FieldName = '" . $_POST["Example"] . "'";


Say if $_POST["Example"] contains this: "' OR 1=1;"

Your query would then end up like this, and always be true, therefore all results will be shown (overriding your statement). the mysql functions escape the quotes to avoid any data being able to get out as string ;)

What your query would end up like:



$sql = "SELECT * FROM tbl_name WHERE FieldName = '' OR 1=1;"; # resulting into true, so all results in tbl_name will be returned.


Take special care when handling data from the database/tables =]

Hope this makes sense,
Shaun

MattF
02-23-2010, 07:50 PM
I take it that I do exactly the same thing for an Insert but what about when I read the table and display the contents? Will the apostophe show up?

You do the same for any query. Unless you're using prepared/parameterised statements, any and all user input should be escaped. When you're selecting the data which is already in the DB, any user input in the query itself, again, should be escaped. The data you are selecting from the DB, however, obviously comes out in the same form as it was before being escaped on the initial insertion.

To put it in a simpler form, any part of a query where '$input=somevalue' or 'somevalue IN($input)' etc, (any part where $input is a selector and derived from user input) should be escaped. Those two are just basic examples, btw.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum