|
PHP - Mysqli Count Query: Prepared Statements
Hi ! I am trying to translate my mysqli count query that works perfectly into prepared statements. Unfortunately, after playing around and using my knowledge of PS, I have come up with this script which fails to execute and returns a http 500 error. I may have missed something very silly, I require some guidance on fixing the error.
<?php
$conn = mysqli_connect("xxxx", "xxxx", "xxxx", "xxx");
$sel_query = "SELECT
S1, B1
COUNT(IF(S1 = ?, 1, NULL)) 'Accepted',
COUNT(IF(S1 = ?, 1, NULL)) 'Rejected',
COUNT(IF(S1 = ?, 1, NULL)) 'Under_Review' FROM Enrol";
$stmt = $conn->prepare($sel_query);
$Accepted="Accepted";
$Rejected="Rejected";
$Under_Review="Under Review";
$stmt->bind_param("sss",$Accepted, $Rejected, $Under_Review);
$stmt->execute();
$result = $stmt->get_result(); // get the mysqli result
if($result->num_rows === 0) exit('No records found!');
while($row = $result->fetch_assoc()) { ?>
<tr>
<td><?php echo $row["Accepted"]; ?></td>
<td><?php echo $row["Rejected"]; ?></td>
<td><?php echo $row["Under_Review"]; ?></td>
</tr>
</table>
Edited June 24, 2020 by PythonHelp Similar TutorialsI have just got hold of a MySQLi class (a wrapper to the built in one of course), and for EVERY query sent they have used prepared statements. Is this right? I expected it to just send using the mysqli_query function (with the prepared statements option if selected). Should you send ALL queries using a prepared statement? What (if any) are the downfalls of using prepared statements? I'm trying to work with prepared statements, but unfortunately I do not get any result back. I also tried while($stmt->fetch()) { ... }, with the same effect.
Does anyone have a suggestion?
Thanks in advance.
<?php
MAKING CONNECTION
if ($stmt = $mysqli->prepare("SELECT year FROM SOMETABLE WHERE id = ? LIMIT 1")) {
$id = $_GET['id'];
$stmt->bind_param("i", $id);
$stmt->execute();
$stmt->bind_result($year);
$stmt->fetch();
echo $year;
$stmt->close();
}
$mysqli->close();
?>
I have a prepared statement class for MYSQL, since in PHP 5 this is now changing to mysqli; I'm looking for some help in changing the code from my existing class to the new mysqli.
I have done some research online about changing from mysql to mysqli but the changes I made seems to only cause issues with connecting to the database.
After many hours of changing the existing file using the research online, I've decided to start again and ask others if they would be ever so kind to help this noob out and point out which parts of the script needs to be changed.
Thank you for reading.
<?php
class Database {
private $host;
private $user;
private $pass;
private $name;
private $link;
private $error;
private $errno;
private $query;
function __construct($host, $user, $pass, $name = "", $conn = 1) {
$this -> host = $host;
$this -> user = $user;
$this -> pass = $pass;
if (!empty($name)) $this -> name = $name;
if ($conn == 1) $this -> connect();
}
function __destruct() {
@mysql_close($this->link);
}
public function connect() {
if ($this -> link = mysql_connect($this -> host, $this -> user, $this -> pass, TRUE)) {
if (!empty($this -> name)) {
if (!mysql_select_db($this -> name, $this->link)) $this -> exception("Could not connect to the database!");
}
} else {
$this -> exception("Could not create database connection!");
}
}
public function close() {
@mysql_close($this->link);
}
public function query($sql) {
if ($this->query = @mysql_query($sql, $this->link)) {
return $this->query;
} else {
$this->exception("Could not query database!");
return false;
}
}
public function num_rows($qid) {
if (empty($qid)) {
$this->exception("Could not get number of rows because no query id was supplied!");
return false;
} else {
return mysql_num_rows($qid);
}
}
public function fetch_array($qid) {
if (empty($qid)) {
$this->exception("Could not fetch array because no query id was supplied!");
return false;
} else {
$data = mysql_fetch_array($qid);
}
return $data;
}
public function fetch_array_assoc($qid) {
if (empty($qid)) {
$this->exception("Could not fetch array assoc because no query id was supplied!");
return false;
} else {
$data = mysql_fetch_array($qid, MYSQL_ASSOC);
}
return $data;
}
public function fetch_object($qid) {
if (empty($qid)) {
$this->exception("Could not fetch object assoc because no query id was supplied!");
return false;
} else {
$data = mysql_fetch_object($qid);
}
return $data;
}
public function fetch_all_array($sql, $assoc = true) {
$data = array();
if ($qid = $this->query($sql)) {
if ($assoc) {
while ($row = $this->fetch_array_assoc($qid)) {
$data[] = $row;
}
} else {
while ($row = $this->fetch_array($qid)) {
$data[] = $row;
}
}
} else {
return false;
}
return $data;
}
public function last_id() {
if ($id = mysql_insert_id()) {
return $id;
} else {
return false;
}
}
private function exception($message) {
if ($this->link) {
$this->error = mysql_error($this->link);
$this->errno = mysql_errno($this->link);
} else {
$this->error = mysql_error();
$this->errno = mysql_errno();
}
if (PHP_SAPI !== 'cli') {
?>
<div class="alert-bad">
<div>
Database Error
</div>
<div>
Message: <?php echo $message; ?>
</div>
<?php if (strlen($this->error) > 0): ?>
<div>
<?php echo $this->error; ?>
</div>
<?php endif; ?>
<div>
Script: <?php echo @$_SERVER['REQUEST_URI']; ?>
</div>
<?php if (strlen(@$_SERVER['HTTP_REFERER']) > 0): ?>
<div>
<?php echo @$_SERVER['HTTP_REFERER']; ?>
</div>
<?php endif; ?>
</div>
<?php
} else {
echo "MYSQL ERROR: " . ((isset($this->error) && !empty($this->error)) ? $this->error:'') . "\n";
};
}
}
?>
Hi,
I have the following query
SELECT user_details.User_club_ID, user_details.fname, user_details.lname, user_details.email, user_details.club_No club.CLUBCODE, club.club_id FROM user_details, club WHERE club_id = $cid AND user_details.club_No = club.CLUBCODE AND user_status = 'active'";which I converted to a prepared statement as SELECT user_details.User_club_ID, user_details.fname, user_details.lname, user_details.email, user_details.club_No club.CLUBCODE, club.club_id FROM user_details, club WHERE club_id = ? AND user_details.club_No = club.CLUBCODE AND user_status = ?";Please note that user_status is a field in the table user_details. The original query (non -PDO) works correctly. I want to know if this is correct and that the comparison in the WHERE clause i.e. user_details.club_No = club.CLUBCODE is security safe. If not then how should this be modified. Also if there is a better way to write this statement, kindly show that as well. Thanks Thanks all ! Edited by ajoo, 11 December 2014 - 02:35 AM. I've searched all over for the past few days trying to figure out what I'm doing wrong. Basically what I'm trying to do is create a prepared statement inside my User class. I can connect to the database, but my query does not execute as expected. Here's the code for my User class Code: [Select] <?php include '../includes/Constants.php'; ?> <?php /** * Description of User * * @author Eric Evas */ class User { var $id, $fname, $lname, $email, $username, $password, $conf_pass; protected static $db_conn; //declare variables public function __construct() { $host = DB_HOST; $user = DB_USER; $pass = DB_PASS; $db = DB_NAME; //Connect to database $this->db_conn = new mysqli($host, $user, $pass, $db); //Check database connection if ($this->db_conn->connect_error) { echo 'Connection Fail: ' . mysqli_connect_error(); } else { echo 'Connected'; } } function regUser($fname, $lname, $email, $username, $password, $conf_pass) { if ($stmt = $this->db_conn->prepare("INSERT INTO USERS (user_fname,user_lname, user_email,username,user_pass) VALUES (?,?,?,?,?)")) { $stmt->bind_param('sssss', $this->fname, $this->lname, $this->email, $this->username, $this->password); $stmt->execute(); $stmt->store_result(); $stmt->close(); } } } ?> And here's the file that I created to instantiate an instance of the user class. Code: [Select] <?php include_once 'User.php'; ?> <?php //Creating new User Object $newUser = new User(); $newUser->fname = $_POST['fname']; $newUser->lname = $_POST['lname']; $newUser->email = $_POST['email']; $newUser->username = $_POST['username']; $newUser->password = $_POST['password']; $newUser->conf_pass = $_POST['conf_pass']; $newUser->regUser($newUser->fname, $newUser->lname, $newUser->email, $newUser->username, $newUser->password, $newUser->conf_pass); ?> And lastly heres the form that I want to get info from the user to insert into the database Code: [Select] <html> <head> <title></title> <link href="stylesheets/styles.css" rel="stylesheet" type="text/css"/> </head> <body> <form action = "Resources/testClass.php" method="post" enctype="multipart/form-data"> <label>First Name: </label> <input type="text" name="fname" id="fname" size="25" maxlength="25"/> <label>Last Name: </label> <input type="text" name="lname" id="lname" size="25" maxlength="25"/> <label>Email: </label> <input type="text" name="email" id="email" size="25" maxlength="40"/> <label>Username: </label> <input type="text" name="username" id="username" size="25" maxlength="32"/> <label>Password: </label> <input type="password" name="password" id="password" size="25" maxlength="32"/> <label>Re-enter Password: </label> <input type="password" name="conf_pass" id="conf_pass" size="25" maxlength="32"/> <br /><br /> <input type="submit" name="submit" id="submit" value="Register"/> <input type="reset" name="reset" id="reset" value="Reset"/> </form> </body> </html> class getjson { function query_db ($mysqli, $search_string){ $query = "SELECT name, location, address FROM csv_table WHERE address LIKE CONCAT('%',?,'%') OR name LIKE CONCAT('%',?,'%') OR location LIKE CONCAT('%',?,'%') LIMIT 0,5"; $stmt = $mysqli->prepare($query); $stmt->bind_param("sss", $search_string, $search_string, $search_string); $stmt->execute(); // $stmt->bind_result($name, $location, $address); // not necessary for code to work $result = $stmt->get_result(); while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){ foreach ($row as $value) { $data[] = array( 'label' => $value .', '. $value.' '. $value , 'value' => $value ); } } return $data; } } $results = new getjson; $results = $results->query_db($mysqli, $search_string); print_r($results); echo count($results); I have the following code. It works, but I cannot get array notation working correctly. For instance, I would like to access $row['address'] to input into my array in the foreach statement. But I can't seem to get it to work after a couple hours. So any ideas appreciated. Thanks, Mark I'm trying to use a prepared statement inside a function and it's giving me some trouble. I have an RPG game I'm working on an want a function to include on pages that tells a user to collect their egg from the professor if they haven't already received it yet. When I try to call the function on the page these are the errors I get:
Notice: Undefined variable: link in /home/evoarena/public_html/Dev/functions.php on line 23 $link is defined in a db config file and on the page I'm trying to call the function I have the files required like this so I'm wondering why it still thinks link is an undefined variable require "config.php"; require "functions.php"; Function Code:
function egg_received () {
if (isset($_SESSION['username'])) {
$username = $_SESSION['username'];
$query = "SELECT egg_received FROM users WHERE username=?";
if ($stmt = $link->prepare($query)) {
$stmt->bind_param("s", $username);
$stmt->execute();
$result = $stmt->get_result();
$egg = $result->fetch_array();
if ($egg['egg_received'] == '0') {
echo "<div class='alert alert-primary' role='alert'>
Oh well hello there! It appears the professor is looking for you today!<br><a href='../world/professor.php'>Visit the Professor</a>
</div>";
require "footer.php";
exit;
}
} else {
$error = $link->errno . ' ' . $link->error;
echo $error;
}
}
}
So I am trying to learn PDO and after some reading, I have found that you cannot use exec with select statements, however, if you are using a prepared statement, you can. However, PDO has a function just selecting the data, using PDO::query. So my question is, how do I know when is the proper used to use PDO::query or the prepared statement route? I have been able to duplicate the queries using both methods and obtain the same results. Is the prepared statement needed for sanitizing the data if I am selecting from user input and PDO:query used if I am only doing a general select where I explicitely state what I am selecting for in the SQL statement? I have a general dbInsert class method that accepts three arguments: table, fields, and values - the last two each as an array. I am using a PDO connection and prepared statements to sanitize everything. I am having a problem with the bindParam() function accepting the parameter ID's though... here's my code and the result I am getting, any advice? My arguments: $this->_table = "testdata"; $this->_fields = array('product_id', 'store_id', 'description', 'price', 'colors', 'sizes'); $this->_values = array("20002157", "2005", "Kids polo shirt", "12.59", "White", "Large"); My object: $_crud = new Crud(); $_makeCrud = $_crud->dbInsert($this->_table, $this->_fields, $this->_values); My class: public function dbInsert($table, $fields, $values) { $_table = $table; $_fields = implode(", ", $fields); // Create and format the list of insert values $_values = ""; // replaces values with "?" placeholders foreach ($values as $value) { $_values .= "?" . ", "; } // trims off last comma and space $_values = substr($_values, 0, -2); // checks database connection if (isset($this->_dbh)) { // Create the SQL Query $this->_sql = 'INSERT INTO ' . $_table . ' (' . $_fields . ') VALUES (' . $_values . ')'; // Build the query transaction $this->_dbh->beginTransaction(); // Build the prepared statement $this->_stmt = $this->_dbh->prepare($this->_sql); // Bind the parameters to their properties foreach ($values as $key => $val) { // starts $key at 1 $key = (int) $key + 1; $this->_stmt->bindParam($key, $val); } // Execute the query $this->_stmt->execute(); $this->_affectedRows = $this->_stmt->rowCount(); } } What my new table row should look like: Code: [Select] product_id store_id description price colors sizes ---------------------------------------------------------------------------------------------- 20002157 2005 kids polo shirt 12.59 white large But this is what my new row DOES look like: Code: [Select] product_id store_id description price colors sizes ---------------------------------------------------------------------------------------------- Large 0 Large 0.00 Large Large So it's taking the value of that last $value and inserting it in all the fields, the store_id and price are 0 and 0.00 because of their numeric type... I assume it is a syntax error, missing quotes somewhere, but where? Hello there
I'm really struggeling with my prepared statement. I want to get the number of unread PMs to show at the user's homepage. Can someone help me with some explanation about my code? I don't really know what I'm doing yet. I've read a lot of code about prepared statements, but I still can't figure it out.
public function count_unread_pm() {
if($stmt = $this->db->prepare("SELECT unread FROM pm WHERE unread=1")) {
/* Bind parameters, s - string, b - blob, i - int, etc */
$stmt->bindParam(':unread', $id, PDO::PARAM_INT);
$stmt -> execute();
var_dump($stmt); // no output
/* Bind results */
$stmt -> bind_result($test); // Error: call to undefined method
/* Fetch the value */
$stmt -> fetch();
$numberofrows = $stmt->num_rows;
/* Close statement */
$stmt -> close();
}
var_dump($numberofrows); // no output
}
Edited September 16, 2019 by Fabel
Heya, i'm a noob looking for some criticism on a bit of code that I wrote. I'm trying to form a function for updating records within a database. I've just recently learned about prepared statements so i'm trying to implement them into my existing code for security reasons. The problem that i'm having with using a function is that I have to pass the table names through variables which can't be used as a parameter for the PS. Therefore leaving me with the question: should I be using a function for this or should I just have multiple PS's throughout my code? Below is what I currently have running. Throughout my code I have arrays generated by forms: if( isset($_POST['edit']) ) { foreach( $_POST as $var => $value ) { $fields[] = $var; $vars[] = $value; } } Then I send the arrays to the function: update( database, table, $fields, $vars, field, null, null, $field, null, null, 'ORDER BY id', 'LIMIT 1', 1 ); Function: function update( $database, $table, $fields, $vars, $field1, $field2, $field3, $value1, $value2, $value3, $order, $limit, $num ) { $database = database( $database ); $table = table( $table ); $order = order( $order ); $limit = limit( $limit ); $num = intval( $num ); $db = new mysqli( 'localhost', '*', '*', $database ); $stmt = $db->stmt_init(); $arraySize = count( $fields ); for( $int = 0; $int < $arraySize; $int++ ) { if( checkInt( $fields[$int], $vars[$int] ) ) { $vars[$int] = intval( $vars[$int] ); $par1 = "i"; } else { $par1 = "s"; } if( $num == 1 ) { if( checkInt( $field1, $value1 ) ) { $value1 = intval( $value1 ); $par2 = "i"; } else { $par2 = "s"; } if( $vars[$int] != 'Submit' ) { $stmt->prepare( "UPDATE $table SET $fields[$int] = ? WHERE $field1 = ? $order $limit" ); $stmt->bind_param( "$par1$par2", $vars[$int], $value1 ); $stmt->execute(); } } } $stmt->close(); $db->close(); } I was told to hardcode all of the variables not being prepared. I'm not positive this is actually helpful though. Any input here would be appreciated. (i've done this for $database, $table, $order, $limit, $num: function table( $value ) { if( empty( $value ) ) { break; } else { $tables = array( 'comments', 'results' ); $key = array_search( $value, $tables ); return $tables[$key]; } } I guess i'm asking if this is OK or am I heading down the wrong direction. I hear using classes is a better solucion but i'm having trouble trying to understand how to use them. Any help would be appreciated, thanks! I am revisiting my code to refactor it and debating if whether or not I should use mysql prepared statements. The only goal I am trying to accomplish, is to simply display menu items for a restaurant food ordering website. That's all. It will select menu items from a database and display them using a while loop.
Upon reading the documentation in php's website. They said that prepared statements are more efficient when statements are repeated.
But all I am doing is querying the table once and preforming a while loop when a user visits my page.
So is it really necessary to use prepared statements other than the fact that is prevents SQL injection? I am totally new to PHP and prepared statements such as PDO and MySQLi usage. I was told to write codes in these because they are more secure than basic coding. I have the following code but I have problem to convert it to MySQLi prepared statement: Code: [Select] <?php include_once 'dbinfo.php'; if(isset($_POST['kuldes'])) { $name = trim($_POST['nev']); $username = $_POST['felh_nev']; $password = $_POST['jelszo']; $email = $_POST['email']; $phone = $_POST['telefon']; $gender = $_POST['sex']; $hobby = $_POST['hobby']; $regfelt = $_POST['regfelt']; $name = strip_tags($name); $name = stripslashes($name); $username = strip_tags($username); $email = strip_tags($email); $phone = strip_tags($phone); $date = date("d-m-Y"); if($name == NULL || $username == NULL || $password == NULL || $email == NULL || $phone == NULL || $gender == NULL) { echo "Please complete the form below or one of the boxes is empty."; } else { if(strlen($username) <= 3 || strlen($username) >= 30){ $final_report.="Your username must be between 3 and 30 characters.."; } else { $select_dbase="SELECT * FROM users WHERE username='$username'"; $result=mysqli_query($connect, $select_dbase); if(mysqli_num_rows($result) != 0){ $final_report.="The username is already in use!";} else { if(strlen($password) <= 6 || strlen($password) >= 12){ $final_report.="Your password must be between 6 and 12 digits and characters.."; } else { if(!eregi("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$", $email)){ $final_report.="Your email address was not valid.."; } else { if(!eregi("^[0-9]{1,3}-[0-9]{1,3}-[0-9]{1,10}$",$phone)){ $final_report.="Phone number is invalid. Only numbers with hyphen. Allowed format: countrycode-areacode-phonenumber"; } else { if(!isset($hobby)){ $final_report.="Youd didn't select any hobbies"; } else { if(!isset($regfelt)){ $final_report.="You didn't accept the terms"; } else { //The implode() function returns a string from the elements of an array. $h = implode(",", $hobby); $insert_dbase = 'INSERT INTO users(name,sex,email,phone_number,username,password,hobby) VALUES("' . $name . '","' . $gender . '","' . $email . '", "' . $phone . '","' . $username . '","' . md5($_POST['jelszo']) . '","'. $h .'")'; mysqli_query($connect,$insert_dbase); header("Location: login_form.php"); exit; }}}}}}}}} ?> <h1>Registration Form</h1> <form action="<?php echo $_SERVER['PHP_SELF']; ?>" name="registration_form" method="POST"> <p>Name: <input type="text" name="nev" value="<?php echo (isset($name) ? $name : ''); ''?>" size=25></p> <p>Username: <input type="text" name="felh_nev" value="<?php echo (isset($username) ? $username : ''); ?>" size=10></p> <p>Password: <input type="password" name="jelszo" size=10></p> <!--<p>Password again:<input type="password" name="password_confirmation"></p>--> <p>E-mail: <input type="text" name="email" value="<?php echo (isset($email) ? $email : ''); ?>"/></p> <p>Phone number: <input type="text" name="telefon" value="<?php echo (isset($phone) ? $phone : ''); ?>"/></p> <p>Sex: <label><input type="radio" name="sex" value="no" >Female</label> <label><input type="radio" name="sex" value="ferfi" >Male</label></p> <p>Favorite hobbies (Using CTRL you can select more than one):</p> <select name="hobby[]" size="4" multiple> <option value="sport">Sport</option> <option value="mozi">Movies</option> <option value="kirandulas">Hiking</option> <option value="olvasas">Reading</option> </select> <!-- <p>Other message:</p> <textarea name="megjegyzes" cols="40"></textarea>--> <p><input name="regfelt" type="checkbox" value="elfogad">I accept the terms!</p> <p><input name="kuldes" type="submit" value="Submit form"> <input name="reset" type="reset" value="delete"></p> <table width="501" border="1"> <tr> <td><?php echo $final_report; ?></td> </tr> </table> <p> </p> </form>My problems are these parts: the following code: Code: [Select] $select_dbase="SELECT * FROM users WHERE username='$username'"; $result=mysqli_query($connect, $select_dbase); if(mysqli_num_rows($result) != 0){ $final_report.="The username is already in use!";}I converted to this Code: [Select] $select_dbase="SELECT * FROM users WHERE username='$username'"; $select_dbase = $mysqli->real_escape_string($select_dbase); $result = $mysqli->query($query); if($result != 0){ $final_report.="The username is already in use!";} $result->close();but without luck, it does not work, it says: Parse error: syntax error, unexpected T_ELSE in I got stucked and I don't even have the chance to convert this part Code: [Select] $insert_dbase = 'INSERT INTO users(name,sex,email,phone_number,username,password,hobby) VALUES("' . $name . '","' . $gender . '","' . $email . '", "' . $phone . '","' . $username . '","' . md5($_POST['jelszo']) . '","'. $h .'")'; mysqli_query($connect,$insert_dbase); header("Location: login_form.php"); exit;of the code to prepared statement: Anyone please who can help me? I swear I had read a tutorial on this site, but I must be mistaken as I can't find it. I did some googling on it.. but not to happy with the tutorials. Just wondering if someone could point me in a better direction. I'm wondering if I can take advantage of these. Like if I put these in a "config" file that is called on every PHP page and I have 2 or 3 SQL queries that I always want to run, a SELECT and UPDATE (maybe a delete). I could call these at will.. right? Without having to rewrite the query a million times? Any help is most appreciated. PDO-Prepared Statements using mysqli_real_escape_string
Is it a good Idea to use mysqli_real_escape_string for extra security In the Prepared Statements
<?php
try {
require_once '../includes/pdo_connect.php';
$make = mysqli_real_escape_string($_GET['make']);
$sql = 'SELECT *
FROM cars
WHERE make LIKE :make AND yearmade >= :yearmade AND price <= :price
ORDER BY price';
$stmt = $db->prepare($sql);
$stmt->bindValue(':make', '%' . $make . '%');
$stmt->bindParam(':yearmade', $_GET['yearmade'], PDO::PARAM_INT);
$stmt->bindParam(':price', $_GET['price'], PDO::PARAM_INT);
$stmt->execute();
$errorInfo = $stmt->errorInfo();
if (isset($errorInfo[2])) {
$error = $errorInfo[2];
}
} catch (Exception $e) {
$error = $e->getMessage();
}
I've had to rearrange a lot of code and I've been trying to put together a prepared statement in a registration form. I'm having a really hard time and being very new to PHP the issue is really confusing for me. first, I have this function: //PROCESS DB
function process_database($post) {
global $table;
global $conn;
//THIS FUCKING THING IS DRIVING ME
//check database connection
if ($conn->connect_error) {
return false;
} else {
if($statement = $conn->prepare("INSERT INTO $table (username, email, password) VALUES ( ?, ?, ? )")){
$username = $post['username'];
$email = $post['email'];
$password = $post['password'];
$statement->bind_param("sss", $username, $email, $password);
$statement->execute();
//DEBUGGING
echo "Added: ".$username.", ".$email.", ".$password."<br>";
if(!$statement->execute()){
printf("Connect Failed: %s\n", $conn->connect_error);
} else {
echo 'fuckin ay!!!';
}
//END DEBUG BLOCK
} else {
return false;
}
}
return true;
}
The issue is very strange. I'll post the function call so it's clear: //process database actions
if (!process_database($data) ) {
return array( 'status' => 0, 'message' => 'Unable to process database request' );
}
When I run the registration.py without process_database() everything is fine, so I'm confident in the error processing. Here's where it get weird - when I process the form Quoteecho "Added: ".$username.", ".$email.", ".$password."<br>"; is returned from the //DEBUGGING BLOCK but I also get back the error from the following if statement - "Connection Failed: ...." BUT I also get back the registration successful message that only shows if the function returns true In short, it's giving me 2 positive affirmations but also the Connection failed message and of course it's not adding anything to the database. I've been working this function all day, and I'm lost for answers. What's going on with this code? I can't see where I've gone wrong Edited July 1 by TechnoDiverin php, pdo, the code does not output any error messages when table is not found.
try {
$stmt = $dbh->prepare("SELECT * FROM 1234");
$stmt->execute();
$row = $stmt->fetch();
} catch (PDOException $e) {
echo $e->getMessage().' in '.$e->getFile().' on line '.$e->getLine();
}
the code only seems to work when the following code is placed just under the "try {"
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);the above code is used just below the connection to the database code. how to get the try - catch code to work without using the setAttribute code every time for prepared statements? In lieu of prepared statements, will this work effectively? Is it overkill? $username = mysqli_real_escape_string($conn,$_POST["username"]); $username = strip_tags(trim($username)); Can I write it this way: $username = mysqli_real_escape_string($conn,strip_tags(trim($_POST["username"]))); Are prepared statements a guarantee for defeating an injection attack? Or should they be used in conjunction with the above (or other) coding to bolster a database's defenses? I wrote a code with prepared statements MySQLi: Code: [Select] <?php include_once 'dbinfo.php'; if(isset($_POST['kuldes'])) { $name = trim($_POST['nev']); $username = $_POST['felh_nev']; $password = $_POST['jelszo']; $email = $_POST['email']; $phone = $_POST['telefon']; $gender = $_POST['sex']; $hobby = $_POST['hobby']; $regfelt = $_POST['regfelt']; $name = strip_tags($name); $name = stripslashes($name); $username = strip_tags($username); $email = strip_tags($email); $phone = strip_tags($phone); $date = date("d-m-Y"); if($name == NULL || $username == NULL || $password == NULL || $email == NULL || $phone == NULL || $gender == NULL) { echo "Please complete the form below or one of the boxes is empty."; } elseif(strlen($username) <= 3 || strlen($username) >= 30){ $final_report.="Your username must be between 3 and 30 characters.."; } elseif($stmt = $connect->prepare('SELECT * FROM users WHERE username=?')) { $stmt->bind_param('s', $username); $stmt->execute(); $stmt->bind_result($username); while ($stmt->fetch()) { printf("Name: %s\n", $name); $final_report.="The username is already in use!"; } $stmt->close(); }elseif(strlen($password) <= 6 || strlen($password) >= 12){ $final_report.="Your password must be between 6 and 12 digits and characters.."; } elseif(!eregi("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$", $email)){ $final_report.="Your email address was not valid.."; } elseif(!eregi("^[0-9]{1,3}-[0-9]{1,3}-[0-9]{1,10}$",$phone)){ $final_report.="Phone number is invalid. Only numbers with hyphen. Allowed format: countrycode-areacode-phonenumber"; } elseif(!isset($hobby)){ $final_report.="Youd didn't select any hobbies"; } elseif(!isset($regfelt)){ $final_report.="You didn't accept the terms"; } else { if ($stmt = $connection->prepare('INSERT INTO users(name,sex,email,phone_number,username,password,hobby) VALUES(?, ?, ?, ?, ?, ?, ?)')) { $stmt->bind_param('sssssss', $name, $sex, $email, $phone_number, $username, $password, $hobby); $stmt->execute(); $stmt->close(); } }}?> <h1>Registration Form</h1> <form action="<?php echo $_SERVER['PHP_SELF']; ?>" name="registration_form" method="POST"> <p>Name: <input type="text" name="nev" value="<?php echo (isset($name) ? $name : ''); ''?>" size=25></p> <p>Username: <input type="text" name="felh_nev" value="<?php echo (isset($username) ? $username : ''); ?>" size=10></p> <p>Password: <input type="password" name="jelszo" size=10></p> <!--<p>Password again:<input type="password" name="password_confirmation"></p>--> <p>E-mail: <input type="text" name="email" value="<?php echo (isset($email) ? $email : ''); ?>"/></p> <p>Phone number: <input type="text" name="telefon" value="<?php echo (isset($phone) ? $phone : ''); ?>"/></p> <p>Sex: <label><input type="radio" name="sex" value="no" >Female</label> <label><input type="radio" name="sex" value="ferfi" >Male</label></p> <p>Favorite hobbies (Using CTRL you can select more than one):</p> <select name="hobby[]" size="4" multiple> <option value="sport">Sport</option> <option value="mozi">Movies</option> <option value="kirandulas">Hiking</option> <option value="olvasas">Reading</option> </select> <p><input name="regfelt" type="checkbox" value="elfogad">I accept the terms!</p> <p><input name="kuldes" type="submit" value="Submit form"> <input name="reset" type="reset" value="delete"></p> <table width="501" border="1"> <tr> <td><?php echo $final_report; ?></td> </tr> </table> <p> </p> </form>And I get the following error message: Warning: mysqli_stmt::bind_result() [mysqli-stmt.bind-result]: Number of bind variables doesn't match number of fields in prepared statement in I don't understand what's the problem is, many people can't give solution for this? Anyone who can help me? It's brain racking. |
|||||||