Is this mysqli secure ??

[websoft08]

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

 

[wildteen88]

Your code will not be prone to SQL Injection attacks as you are not using any data coming form the user in the your queries.

 

If you have hard coded queries in your app then they cannot be hijacked. Only queries which use user data is prone to SQL Injection attacks, eg:

$username = $_POST['username'];
$password = $_POST['password'];
$query = "SELECT * FROM users_table WHERE username='$username' AND password='$password'";

The query used above is prone to SQL Injection as we are using raw user data in the query. For example someone could enter ' OR 1=1 --' as the username. When our script runs the query becomes (user input is highlighted in red):

SELECT * FROM users_table WHERE username='' OR 1=1 -- AND password='password used'

Which tells mysql to Select all rows from the users table where username is blank or select the first entry from the table. It will ignore the rest of the query as mysql sees -- as a comment.

 

This is a perfectly valid SQL Injection attack and very serious one too. There are far more worse ones however there is a very simple way to prevent this attact and thats to use  a function called mysql_real_escape_string, eg:

$username = mysql_real_escape_string($_POST['username']);
$password = mysql_real_escape_string($_POST['password']);
$query = "SELECT * FROM users_table WHERE username='$username' AND password='$password'";

Now any quotes (or any other malicious characters) which can affect our query are escaped and thus our very unsecure script is now secured.

 

There are many articles on the web which explains and provides tips to prevent SQL Injection.