Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7

Thread: An apostophe

  1. #1
    New Coder
    Join Date
    Dec 2002
    Posts
    31
    Thanks
    1
    Thanked 0 Times in 0 Posts

    An apostophe

    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);

  • #2
    Regular Coder bacterozoid's Avatar
    Join Date
    Jun 2002
    Location
    USA
    Posts
    490
    Thanks
    24
    Thanked 35 Times in 35 Posts
    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.my...ape-string.php

  • #3
    New Coder
    Join Date
    Feb 2010
    Location
    UK, North West
    Posts
    36
    Thanks
    0
    Thanked 5 Times in 5 Posts
    Hello,
    Take a look at this:

    PHP Code:

    $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:
    PHP Code:
    need1=$need1need2=$need2need3=$need3need4=$need4need5=$need5need6=$need6need7=$need7need8=$need8need9=$need9need10=$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

  • Users who have thanked Shauny_B for this post:

    rowantrimmer (02-23-2010)

  • #4
    New Coder
    Join Date
    Dec 2002
    Posts
    31
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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?

  • #5
    New Coder
    Join Date
    Dec 2002
    Posts
    31
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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?

  • #6
    New Coder
    Join Date
    Feb 2010
    Location
    UK, North West
    Posts
    36
    Thanks
    0
    Thanked 5 Times in 5 Posts
    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:

    PHP Code:
    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:

    PHP Code:
    $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:

    PHP Code:
    $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

  • #7
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    Quote Originally Posted by rowantrimmer View Post
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •