1

So I've recently switched to using MySQLi. And I have a few questions about it.

My current code to fetch a data is

$current = "SELECT * FROM hi where username='me'";
$result = $connect->query($current) or die($mysqli->error.__LINE__);
$row = $result->fetch_assoc();

and my execution of a query is

$current = "SELECT * FROM hi where username='me'"; $result = $connect->query($current) or die($mysqli->error.LINE);

First question, is it the correct way?

And how is it more secured than the mysql_ method? (I heard it prevents injections or something)

Lastly, apart from using MySQLi now, what else should I do? Is my code secured enough?

I previously used mysql_real_escape_string(htmlspecialchars($string) on any data which will be used in the mysql query, do I still need to use them? Since I'm using MySQLi now.

What other things should I take note of to make my site secured? htmlspecialchars any user inputs?

Thanks!

  • [What Every Programmer Absolutely, Positively Needs To Know About Encodings And Character Sets To Work With Text](http://kunststube.net/encoding/) – deceze Feb 25 '13 at 07:51
  • http://www.phptherightway.com/#databases – deceze Feb 25 '13 at 07:51
  • possible duplicate of [How to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) – deceze Feb 25 '13 at 07:52
  • http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php – deceze Feb 25 '13 at 07:53

1 Answers1

2

about your mysqli_query use :

And how is it more secured than the mysql_ method? (I heard it prevents injections or something)

Here you do not have injection issue as you do not use any user input. So mysql_ or mysqli_ seems equivalent.

about injection issues :

However, if you have to use user input (such as $_POST or $_GET variables) it is better to use prepared statements. Their syntax is this one :

$query = SELECT * FROM mytable WHERE col1=? AND col2=?;
$stmt = $db->prepare($query);
$stmt->bind_param('ss',$_POST['col1'],$_POST['col2']);//I put 2 s because I want 2 params. s stands for strings, i for integer. For that part PHP and mysqli extension offers you two alternative syntaxes. They give the same result but it depends on what you understand the best.

  • first : using bind_result. This function has to be called before execute. It will bind your result to the variables you give in parameters. First column will fill in the first variable, second column the second parameter... The major issue is that it cuts you from the whole mysqli_result API which has fetch_all, fetch_assoc or fectch_object.

    $stmt->bindResult($array['col1'], $array['col2'], $array['col3']);
$stmt->execute();
while($stmt->fetch_assoc()){
   var_dump($array);
}

  • second : using get_result. It allows you to use the mysqli_result API that you are already using when you use $db->query(). It is only available when you use mysqlnd as your driver. If you are under php 5.3+ this is the default driver but with php 5.1 or 5.2 (basically, 1&1 hoster is under 5.2) this is not always like that. 

    $stmt->execute();
$result = $stmt->get_result()->fetch_all();
foreach($result as $row){
   //handle your rows
}

about escaping :

mysql_real_escape_string(htmlspecialchars($string))

In mysqli, you can use mysqli_real_escape_string. The htmlspecialchars function is useless when it comes about database. It is only for display.

Squazz
  • 3,912
  • 7
  • 38
  • 62
artragis
  • 3,677
  • 1
  • 18
  • 30
  • the mysql libraries aren't equivalent here, since mysqli uses a connection to escape – Amelia Feb 25 '13 at 08:05
  • In his query he does not use mysql_real_escape_string. Moreover, as of php 5.3 it is ok (and perhaps mandatory) to give a connection paramter in mysql_real_escape_string function. mysqli's strengh is in persistant connection, prepared statement and MYSQL 5.1+ functionalities support. – artragis Feb 25 '13 at 08:09
  • ah, my bad. mysqli > mysql, but i never new about connection escaping in ext/mysql – Amelia Feb 25 '13 at 08:16
  • Hey! I am having difficulties understanding the prepared statement part :( will it work the same if I just mysql escape my POST or GET? $current = "UPDATE hi set user=$user"; $exec = $connect->query($current) or die($mysqli->error.LINE); $user will be mysql real escaped. –  Feb 25 '13 at 08:35
  • @Jason No. Read the wealth of information I linked to under your question. Learn prepared statements. They're not that hard. – deceze Feb 25 '13 at 08:45
  • I've read... $stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?'); $stmt->bind_param('s', $name); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { // do something with $row } but why is there queries after execute like get result? If i an merely inserting something into the db, do i just use the code untill the $stmt execute? –  Feb 25 '13 at 08:51
  • You can use `get_result` instead of bind_result if you want. It is your choice, they do the same thing. I will put this code in my answer as an alternative. – artragis Feb 25 '13 at 09:01
  • Hey! Another question, so I use a prepared statement whenever theres USER INPUT. Whether it's fetching data and inserting? Whats your email? I need to consult you about something :( –  Feb 25 '13 at 12:06
  • User input has nothing to do with insert or fetch. If you need a data entered by a user to fetch something, you have to escape it so you will use prepared statement. If you want to insert/update datas from a form you will use prepared statement. – artragis Feb 25 '13 at 12:36
  • Hey! I'm still exploring, is this correct? [For Inserting into db] `include("inc/connect.php"); $one = $_GET['one']; $two = $_GET['two']; $stmt = $connect->prepare("INSERT into test SET lol=?, ffd=?"); $stmt->bind_param('ss',$one,$two); $stmt->execute();` As for the fetching, I get this error `Fatal error: Call to undefined method mysqli_stmt::get_result()` when using the get_results method. Also get a similar error when using the binding method... –  Feb 25 '13 at 12:40
  • there is no "result" on a INSERT query. Moreover it is only available with mysqlnd driver, not ext/libmysql. – artragis Feb 25 '13 at 12:50
  • I meant, is my above code correct? [It's for inserting using prepared stmt]. I tried your select method, but it did not work, showing the above error. –  Feb 25 '13 at 13:02
  • which version of PHP have you got? – artragis Feb 25 '13 at 17:49