MySQL vs MySQLi vs Rest

[Cuchara2]

I've been looking into security for PHP and came across MySQLi prepared statements. After doing some research I'm confused about why you would use these instead of normal SQL query. A prepared statement looks something like this:

$quer=$db->stmt_init();

    if($quer->prepare("INSERT INTO `test` (`first`,`second`,`third`) VALUES (?,?,?)")) {

    $quer->bind_param('iss',$aa,$bb,$cc);
    $aa=1;
    $bb="something";
    $cc="testdata";
    $quer->execute();
    $quer->close();
}

How is this in any way beneficial compared to the following:

    $aa=(int)1;
    $bb=(string)mysql_real_escape_string("something");
    $cc=(string)mysql_real_escape_string("testdata");

    if($quer=@mysql_query("INSERT INTO `test` (`first`,`second`,`third`) VALUES ('$aa','$bb','$cc')")) {
        // Carry on
}
else die("Error with database!");

As the prepared statement will require two requests to the MySQL server and falsely suggests that no sanitisation of data is needed - completely ignoring other attacks such as XSS, file inclusions etc.

Edit:
I also meant to ask if there are any other methods I can (feasibly) use for databases in general!

[deceptikon]

After doing some research I'm confused about why you would use these instead of normal SQL query.

Separation of the query and data is a primary method of defending against SQL injection attacks. There are other reasons for using prepared statements, but since your concern is security, that's basically it.

falsely suggests that no sanitisation of data is needed - completely ignoring other attacks such as XSS, file inclusions etc.

Only if you don't understand what the point of prepared statements is, but it's a valid point that some people will think that handling injection attacks is enough, or assume that by using a library all other possible attacks are mitigated as well. That's less of an issue with the library than it is an issue with the programmer though. ;)

You need to validate your input data robustly. Just cleaning or using prepared statements won't do this for you most of the time. Check data types and formats. All input is suspect - treat them as though they are malicious. php has native filtering and sanitizing functions, however, building or using a 3rd party validation class could be of benefit. As noted, it's not just about XSS. Input can be used in a variety of ways, not just as a mysql value.