Is this mysqli secure ??

Discussion in 'PHP' started by websoft08, May 29, 2008.

0
  1. #1
    I am hoping somebody can help me. I have been reading up on how to prevent sql injections and I am still confused about difference between mysqli "object oriented" & "prepared statements". The thing is I am lost as to whether or not "object oriented mysqli" alone is good enough to prevent against mysql injection attacks. For example is this code below which is object oriented secure enough against all sql injections or at least as secure as a prepared statement?:

    function cCnx()
    {
    $bgCnx = new mysqli("localhost", "root", "", "test");

    if (mysqli_connect_errno())
    {
    echo "Error ". mysqli_connect_error();
    exit();
    }
    else
    {
    return $bgCnx;
    }
    }



    function cGet_Countries()
    {
    $cnx_am = cCnx();
    $query = "SELECT FULLNAME,CC1 FROM countries WHERE CC1 != 'US' and CC1 != 'CA' ORDER BY FULLNAME";
    $result = $cnx_am->query($query);
    $countries[0]['num'] = $result->num_rows;
    $i = 1;
    while($result2=$result->fetch_array())
    {
    $countries[$i]['name'] = $result2['FULLNAME'];
    $countries[$i]['cod'] = $result2['CC1'];
    $i++;
    }
    return $countries;
    }



    I hope you can help me understand as I am lost on this and I just want to make sure that when I am coding the site from the beginning that I am doing it in the safest most efficient way to prevent sql injections as well as optimize speed for end users.

    Thanks,
    Will
     
    websoft08, May 29, 2008 IP
  2. Greg Carnegie

    Greg Carnegie Peon

    Messages:
    385
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    0
    #2
    When it comes to SQL injection there is no difference between mysql and mysqli, because the problem is not the interface you use to connect to the databases (eg. mysql or mysqli) but the query itself.

    So, your query is 100% safe from SQL injection because there are no variables inserted in the query. However if you would do something like that:
    
$query = "SELECT FULLNAME,CC1 FROM countries WHERE CC1 != 'US' and CC1 != '".$_POST['country']."' ORDER BY FULLNAME";
    PHP:
    it would be insecure because you are not checking what someone can put into your query, so for example he can terminate current querry with ';' and start new one, for example DELETE FROM countries.

    Hope that helps.
     
    Greg Carnegie, May 29, 2008 IP
  3. lanmonkey

    lanmonkey Active Member

    Messages:
    549
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    60
    #3
    you make user input safe to put in sql queries by escaping it, and in the case of mysql php has a (superb) function built in for this:
    http://uk.php.net/mysql_real_escape_string

    you use it like so:

    
$query = "SELECT FULLNAME,CC1 FROM countries WHERE CC1 != 'US' and CC1 != '".mysql_real_escape_string($_POST['country'])."' ORDER BY FULLNAME";
    PHP:
    it protects you by putting a slash before risky characters (escaping) such as " or '.
     
    lanmonkey, May 29, 2008 IP