Jump to content

Search the Community

Showing results for tags 'prepared statements'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • Welcome to PHP Freaks
    • Announcements
    • Introductions
  • PHP Coding
    • PHP Coding Help
    • Regex Help
    • Third Party Scripts
    • FAQ/Code Snippet Repository
  • SQL / Database
    • MySQL Help
    • PostgreSQL
    • Microsoft SQL - MSSQL
    • Other RDBMS and SQL dialects
  • Client Side
    • HTML Help
    • CSS Help
    • Javascript Help
    • Other
  • Applications and Frameworks
    • Applications
    • Frameworks
    • Other Libraries
  • Web Server Administration
    • PHP Installation and Configuration
    • Linux
    • Apache HTTP Server
    • Microsoft IIS
    • Other Web Server Software
  • Other
    • Application Design
    • Other Programming Languages
    • Editor Help (PhpStorm, VS Code, etc)
    • Website Critique
    • Beta Test Your Stuff!
  • Freelance, Contracts, Employment, etc.
    • Services Offered
    • Job Offerings
  • General Discussion
    • PHPFreaks.com Website Feedback
    • Miscellaneous

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests


Age


Donation Link

Found 8 results

  1. HI All, I am writing a prepared statement to update some user information. Included in this table are the username and password fields. In this particular form, i dont want the user to have access to this information and have built a form that only shows what i want them to be able to change. The bit that i am not sure about is the prepared statement that i am writing. I am getting a boolean error suggesting that my prepare failed and i think this may be because i have not named every field in the table. To give an idea of the table fields i have pulled this from php my_admin (this is not the sql i am running) UPDATE `ssm_user` SET `user_id`=[value-1],`user_email`=[value-2],`user_password`=[value-3], `user_firstname`=[value-4],`user_lastname`=[value-5],`user_accountlevel`=[value-6], `user_mobile`=[value-7],`user_role`=[value-8],`user_lastlogondate`=[value-9] WHERE 1 my prepared statement is $stmt = $conn->prepare(" UPDATE ssm_user SET user_email=?, user_firstname=?, user_lastname=?, user_accountlevel=?, user_mobile=?, WHERE user_id = ? "); $stmt->bind_param('sssssi', $email, $fname, $lname, $accountlevel, $mobile, $uid); $stmt->execute(); return $stmt->affected_rows; Do i have to declare every field in the table or is there something that i am missing here.
  2. Hi all !, I am stuck on the following piece of code which does not give an error nor does it give a result. ( i.e. it gives 0 num_rows which should be > 1). If, however, I execute the query in phpmyadmin by simply substituting the values of $pp,$ll and $room_no in the query it gives the correct result. Please can someone tell me what I may be doing wrong here. Thanks ! $fcon = mysqli_connect($db_host,$db_user,$db_pass,$db_database) or die('Unable to establish a DB connection'); $pp = "(ms.level = 'Beginner' || ms.level = 'Intermediate')"; $ll = 'ms.diff <= 7'; $room_no = 4; $query = "SELECT md.Member_reg_id, md.fname, md.lname, md.email, md.cell, ms.level, ms.diff, ms.score, r.ID_Status FROM register as r JOIN member_detail as md ON r.ID = md.Member_reg_id JOIN memstatus as ms On r.ID = ms.ID WHERE r.CENTERCODE = ? AND r.ID_Status ='A' AND ? AND ? ORDER by level, diff, score DESC"; $stmt=$fcon->prepare($query); $stmt->bind_param('iss',$room_no,$pp,$ll); if(!$stmt->execute()) die('Failed to execute the query'.$fcon->error); else { echo "Executed"; $stmt->bind_result($Member_reg_id,$fname,$lname,$email,$cell,$level,$diff ,$score,$ID_Status); $numrows = $stmt->num_rows; $stmt->store_result(); // echo $numrows; while($stmt->fetch()) { echo "<br>".$fname.' '.$lname; echo "<br>".$level; echo "<br>".$diff; echo "<br>".$score; echo "<br>".$cell; echo "<br>".$email; } }
  3. i have a table of say 100 entries. i want to delete all but the 10 newest of those rows with a prepared statement. im fairly new with prepared statements so the syntax with a subquery is throwing me a bit. $stmt = $db_connect->prepare('DELETE FROM table WHERE owner=? AND owner NOT IN (SELECT owner FROM table ORDER BY dob DESC LIMIT 10)'); $stmt->bind_param('i', 0); $stmt->execute(); $stmt->close();
  4. <?php if(isset($_POST['submit'])){ $uname = $_POST['username']; $pword = $_POST['password']; /*** mysql hostname ***/ $hostname = 'localhost'; /*** mysql username ***/ $username = 'root'; /*** mysql password ***/ $password = 'anty90'; try { $link = new PDO("mysql:host=$hostname;dbname=gambling", $username, $password); /*** echo a message saying we have connected ***/ echo 'Connected to database<br />'; /*** INSERT data ***/ $stmt = $link->prepare("INSERT INTO gamb(username, password) VALUES (?, ?)"); try{ $stmt->execute(array("$uname", "$pword")); } catch(PDOException $e){ echo "Exception caught: $e"; } /*** echo the number of affected rows ***/ //echo $count; /*** close the database connection ***/ $link = null; } catch(PDOException $e) { echo $e->getMessage(); } } ?> <html> <form action='home.php' method='post'> <input type="text" name="username" > <input type="password" name="password" > <input type="submit" name="submit" value="submit"> </form> </html> I'm new to databse programming so I was just wondering if this was vulnerable to sql injection or not.
  5. I have a question re a project I'm doing in php/mysql. I am designing a site where users can record their financial accounts (bank account/credit cards etc) and the transactions and their type that they undertake on a monthly basis on each one of them. What I want is that the user selects a particular account and using GET I can take the account id, and use that to get all transactions associated with that account name from a different table in the database. I get the error trying to get the property of non-object and I'm unsure where I have gone wrong. Ive put some echo comments in previously and can see the GET is working and the initial statement works and I can retrieve the Accname using the ID. It appears to be the next part where I take that Accname that I have got and try to use it to get data from another table that I run into problems. Here is my code, apologies if its ugly I am really only learning this and piecing together bits and pieces from various online tutorials. <?php //connect to database include ('connect-To-db.php'); $id = $_GET['id']; if($stmt = $mysqli->prepare("SELECT `Accname` FROM `Account` WHERE `accID`=?")){ $stmt->bind_param("i", $id); $stmt->execute(); $stmt->bind_result($Accname); while($stmt->fetch()){ $query = $mysqli->query("SELECT `Credit`, `Debit`, `Catagory` FROM `transactions WHERE `Accname`=$Accname"); if($query->num_rows>0){ echo "Yes"; } else{ echo "No"; } } else { echo "ERROR"; }
  6. I am working on converting legacy queries to mysqli prepared statements. My SELECT queries are working fine, but ADD/DELETE/UPDATE are giving me problems. I have found many online examples showing bind statements for ADD/DELETE/SELECT, including the use of call_user_func_array() but others claim that bindings are not necessary when writing records (as opposed to reading them). So I am confused about this issue, and when I use bind statements I get the following error message: Warning: mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement in /srv/www/htdocs/PNS/Complex/writeRec5.php on line 177 bind_param()error : The following are snippets of code using dummy values to simplify the code for testing along with output. I reaIize that call_user_func_array() is not necessary in this simple example, but I will need it for the project. I don't see the error, and I have compared it to many online forum examples which claim to be functioning. Does anything stand out? $prepareString = "Address=?,Permit_Rate=?"; echo "<b>PrepareString: </b>",$prepareString,"<br /><br />"; $Address="123 Somewhere Street";$Permit_Rate=25; $sql="UPDATE table1 SET " . $prepareString . " WHERE Key_ID = " . $keyid; echo $sql,"<br /><br />"; Output: PrepareString: Address=?,Permit_Rate=? UPDATE table1 SET Address=?,Permit_Rate=? WHERE Key_ID = 2664 $stmt = $mysqli->prepare($sql); if (false === $stmt) {die('Query Prep Failed' . $mysqli->error);} $dataTypes="si";$bindParams=array(&$Address,&$Permit_Rate); echo "<b>Length of dataTypes: </b> ",strlen($dataTypes),": <b>Contents of dataTypes: </b>",$dataTypes,"<br />"; echo "<b>Count of bindParams: </b>",count($bindParams)," <b>Contents of bindParams: </B>"; var_dump($bindParams); echo "<br /><br />"; Output: Length of dataTypes: 2: Contents of dataTypes: si Count of bindParams: 2 Contents of bindParams: array(2) { [0]=> &string(20) "123 Somewhere Street" [1]=> &int(25) } $x = call_user_func_array(array($stmt, 'bind_result'), &$bindParams); if (false === $x) {die('bind_param()error :' . $mysqli->error);} $stmt->execute();$stmt->close(); Output: Warning: mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement in /srv/www/htdocs/PNS/Complex/writeRec5.php on line 177 bind_param()error :
  7. The next step in my web site Dev work is converting over to prepared statements. I was wondering if someone could help with some pretty general questions. Some Basic Info: - DB: MySQL - 5.5.25 Source distribution - PHP: 5.4.4 Below is some code I want to make into prepared statements. It's simple code querying a DB displaying results. - My PHP pages are calling functions that are written in another functions file which returns data and then displayed. For example - call from my PHP page: //Get the Summary for the Job $jb_summary_set = get_jb_summ($row["job_id"]); if(!mysqli_num_rows($jb_summary_set) == 0){ echo "<strong>Summary</Strong><br />"; while ($job_summ = mysqli_fetch_array($jb_summary_set)){ echo $job_summ['summary']; } echo "<br /> "; } Function: function get_jb_summ($job_id){ global $connection; //Escape value $job_id = mysqli_real_escape_string($connection, $job_id); $jb_summ_set = mysqli_query($connection, "select summary from job where job_id = '$job_id' and summary !=\"\""); return $jb_summ_set; I've done some reading on prepared statements and have an idea on the purpose and syntax... But what I don't fully understand is where do I put the pieces in my code? Do I put my prepared statements at the top of my functions file and when that file gets loaded (includes statement in my main php page) will the statements be compiled and stored, then called from a function? Or do I put them inside the function, or not use a separate functions file at all?? I would prefer to not have the queries in my php pages if possible. Also I have another site I use for a small business I run... I only have one PHP page now that connects to another DB which displays some simple figures - Invoice totals, payements received, some other related info... Those queries I have as stored procedures inside the DB. Can I have prepared statements in the stored procedures? I know I'm mixing a number of things here (where my confusion is coming form). I'm trying to get a process down so I can quickly and easily copy that code for a quick web page to display data... Once I get this understood I'll start making templates for more visually appealing pages...
  8. I've just completed my first prepared statement, converted to using them for security reasons. I've tested it and it works however I am looking for feedback (constructive criticism welcomed) regarding the code itself although I understand it 's fairly basic. Here's teh code: <?php //prepared statement example include 'database.php'; $query = "SELECT ID FROM users WHERE email = ?"; $email = 'myemail@gmail.com'; $statement = mysqli_stmt_init($connect); mysqli_stmt_prepare($statement, $query); mysqli_stmt_bind_param($statement, 's', $email); mysqli_stmt_execute($statement); mysqli_stmt_bind_result($statement, $id); mysqli_stmt_fetch($statement); echo $id; ?> Also, is using mysqli_stmt_close necessary? Am I correct in saying that without using this function I will not be able to create another prepared statement within that script? - because I have tried the latter and it wouldn't work unless I did close the statement.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.