Call to a member function bindParam() on a non-object MySQLi

Question

Pardon me, I am new to SQL. I have built a form that allows users to input values.

I am trying to remove the possibility of SQL injection, therefore taking the appropriate measures in my programming.

Here is my code using $POST, vulnerable to SQL injection:

$dbh=mysqli_connect("localhost","root","","toplist");

// Checking the connection
if (mysqli_connect_errno($dbh))
  {
  echo "Could not connect do SQL database: " . mysqli_connect_error();
  }

$serverip=$_POST['post_serverip'];
$serverport=$_POST['post_serverport'];
$servertitle=$_POST['post_servertitle'];
$serverdesc=$_POST['post_serverdesc'];
$serverwebsite=$_POST['post_serverwebsite'];
$listingtype=$_POST['post_listingtype'];
$query = mysqli_query($con,"INSERT INTO servers (serverip, serverport, servertitle,     serverdesc, serverwebsite, listingtype) VALUES     ('$serverip','$serverport','$servertitle','$serverdesc','$serverwebsite','$listingtype')");

I heard prepared statements reduce/disable the risk of SQL injection, so I converted my code over using prepared statements:

$dbh=mysqli_connect("localhost","root","","toplist");
global $DBH; 
// Checking the connection
if (mysqli_connect_errno($dbh))
  {
  echo "Could not connect do SQL database: " . mysqli_connect_error();
  }

// Inserting input values into its respected row serverip, serverport, servertitle,     serverdesc, serverwebsite, listingtype
$stmt = $dbh->prepare("INSERT INTO servers (name, value) VALUES (:serverip, :serverip,     :serverport, :serverport, :server desc, :server desc, :serverwebsite, :serverwebsite,     :listing type, :listing type)");
$stmt->bind_param(':post_serverip', $serverip);
$stmt->bind_param(':post_serverport', $serverport);
$stmt->bind_param(':post_servertitle', $servertitle);
$stmt->bind_param(':post_serverdesc', $serverdesc);
$stmt->bind_param(':post_serverwebsite', $serverwebsite);
$stmt->bind_param(':post_listingtype', $listingtype);

But I get the error: Call to a member function bindParam() on a non-object .. Everything else seems to be fine, because this is the only error I receive.

I have done some searching and I think the error may be caused by the connection to my database. Does any body know for sure what this is caused by?

Answer 1

EDIT

$serverip=$_POST['post_serverip'];
$serverport=$_POST['post_serverport'];
$servertitle=$_POST['post_servertitle'];
$serverdesc=$_POST['post_serverdesc'];
$serverwebsite=$_POST['post_serverwebsite'];
$listingtype=$_POST['post_listingtype'];

$mysql_hostname = 'xxx';
$mysql_username = 'xxx';
$mysql_dbname = 'xxx';
$mysql_password = '';

$dbh= new PDO("mysql:host=$mysql_hostname;dbname=$mysql_dbname", $mysql_username, $mysql_password);

$stmt = $dbh->prepare("INSERT INTO servers (serverip, serverport, servertitle, serverdesc, serverwebsite, listingtype)
            VALUES (:serverip, :serverport, :servertitle, :serverdesc, :serverwebsite, :listingtype)");

$stmt->bindParam(':serverip', $serverip);
$stmt->bindParam(':serverport', $serverport);
$stmt->bindParam(':servertitle', $servertitle);
$stmt->bindParam(':serverdesc', $serverdesc);
$stmt->bindParam(':serverwebsite', $serverwebsite);
$stmt->bindParam(':listingtype', $listingtype);
$stmt->execute();

---

Numbered array method

$serverip=$_POST['post_serverip'];
$serverport=$_POST['post_serverport'];
$servertitle=$_POST['post_servertitle'];
$serverdesc=$_POST['post_serverdesc'];
$serverwebsite=$_POST['post_serverwebsite'];
$listingtype=$_POST['post_listingtype'];

$mysql_hostname = 'xxx';
$mysql_username = 'xxx';
$mysql_dbname = 'xxx';
$mysql_password = '';

$dbh= new PDO("mysql:host=$mysql_hostname;dbname=$mysql_dbname", $mysql_username, $mysql_password);

$stmt = $dbh->prepare("INSERT INTO servers (serverip, serverport, servertitle, serverdesc, serverwebsite, listingtype)
            VALUES (:serverip, :serverport, :servertitle, :serverdesc, :serverwebsite, :listingtype)");

$stmt->bindParam(1, $serverip);
$stmt->bindParam(2, $serverport);
$stmt->bindParam(3, $servertitle);
$stmt->bindParam(4, $serverdesc);
$stmt->bindParam(5, $serverwebsite);
$stmt->bindParam(6, $listingtype);

$stmt->execute(array(
':serverip' => $serverip, 
':serverport' => $serverport, 
':servertitle' => $servertitle, 
':serverdesc' => $serverdesc, 
':serverwebsite' => $serverwebsite, 
':listingtype' => $listingtype));
        

---

Original answer to help out OP with examples

Try it like this and replace column_1 and value_1 etc. to match your columns and variables.

N.B.: Use underscores and not hyphens in case you want to space out words.

Method #1

$mysql_hostname = 'xxx';
$mysql_username = 'xxx';
$mysql_dbname = 'xxx';
$mysql_password = '';

$dbh= new PDO("mysql:host=$mysql_hostname;dbname=$mysql_dbname", $mysql_username, $mysql_password);

$stmt = $dbh->prepare("INSERT INTO servers (column_1, column_2, column_3, column_4, column_5, column_6)
            VALUES (:value_1, :value_2, :value_3, :value_4, :value_5, :value_6)");
            
$stmt->bindParam(':column_1', $value_1);
$stmt->bindParam(':column_2', $value_2);
$stmt->bindParam(':column_3', $value_3);
$stmt->bindParam(':column_4', $value_4);
$stmt->bindParam(':column_5', $value_5);
$stmt->bindParam(':column_6', $value_6);
$stmt->execute();

---

"OR" this method: (Method #2)

Don't mix them both together. Use one or the other.

NOTE: Keep it like this $stmt->bindParam(1, $serverip); etc. keeping the 1,2,3,4,5,6 and then replacing $value_x by $serverip etc.

$mysql_hostname = 'xxx';
$mysql_username = 'xxx';
$mysql_dbname = 'xxx';
$mysql_password = '';

$dbh= new PDO("mysql:host=$mysql_hostname;dbname=$mysql_dbname", $mysql_username, $mysql_password);


$stmt = $dbh->prepare("INSERT INTO servers (column_1, column_2, column_3, column_4, column_5, column_6)
            VALUES (:value_1, :value_2, :value_3, :value_4, :value_5, :value_6)");

$stmt->bindParam(1, $value_1);
$stmt->bindParam(2, $value_2);
$stmt->bindParam(3, $value_3);
$stmt->bindParam(4, $value_4);
$stmt->bindParam(5, $value_5);
$stmt->bindParam(6, $value_6);

$stmt->execute(array(
':column_1' => $value_1, 
':column_2' => $value_2, 
':column_3' => $value_3, 
':column_4' => $value_4, 
':column_5' => $value_5, 
':column_6' => $value_6));