Question 263554 in PHP Question posted in
Official manual can be found here.

Php – Prepared statement INSERT

I get the following error:

PHP Fatal error:  Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':name, :cate, :content, :thumbnail, :price, :sale_price, :sale_ratio, :cnt, :...' at line 2 in C:\xampp\htdocs\craft sutra\theme\admin\product\product_ok.php:84

Ive been trying to insert some data in a prepared statement after receiving some comments that
my sql is prone to SQL injections.

database connection:

<?php 
 // Our database config 
 $host = "localhost";
 $dbname = "craft sutra";
 $username = "root";
 $password = "";

 // Connect to our MySQL database
 $conn = mysqli_connect($host, $username, $password, $dbname);

 // Check connection
 if (!$conn) {
   die("Connection failed: " . mysqli_connect_error());
 }

So here I have edited the codes:

$query = "INSERT INTO 제품 (name, cate, content, thumbnail, price, sale_price, sale_ratio, cnt, sale_cnt, isnew, isbest, isrecom, ismain, locate, userid, sale_end_date, reg_date, delivery_fee) VALUES (:name, :cate, :content, :thumbnail, :price, :sale_price, :sale_ratio, :cnt, :sale_cnt, :isnew, :isbest, :isrecom, :ismain, :locate, '".$_SESSION['AUID']."', :sale_end_date, now(), :delivery_fee);";

$stmt = $conn->prepare($query);

$stmt->bindParam(":name", $name);
$stmt->bindParam(":cate", $cate);
$stmt->bindParam(":content", $content);
$stmt->bindParam(":thumbnail", $thumbnail);
$stmt->bindParam(":price", $price);
$stmt->bindParam(":sale_price", $sale_price);
$stmt->bindParam(":sale_ratio", $sale_ratio);
$stmt->bindParam(":cnt", $cnt);
$stmt->bindParam(":sale_cnt", $sale_cnt);
$stmt->bindParam(":isnew", $isnew);
$stmt->bindParam(":isbest", $isbest);
$stmt->bindParam(":isrecom", $isrecom);
$stmt->bindParam(":ismain", $ismain);
$stmt->bindParam(":locate", $locate);
$stmt->bindParam("'".$_SESSION['AUID']."'", $userid);
$stmt->bindParam(":sale_end_date", $sale_end_date);
$stmt->bindParam("now()", $reg_date);
$stmt->bindParam(":delivery_fee", $delivery_fee);
$stmt->execute();

This is my original query without using prepared statements:
How can I apply prepared statements correctly?
I have mysqli connection

$sale_cnt = 0;//판매량
$query="INSERT INTO products
(name, cate, content, thumbnail, price, sale_price, sale_ratio, cnt, sale_cnt, isnew, isbest, isrecom, ismain, locate, userid, sale_end_date, reg_date, delivery_fee)
VALUES('$name'
, '".$cate."'
, '".$contents."'
, '".$thumbnail."'
, '".$price."'
, '".$sale_price."'
, '".$sale_ratio."'
, ".$cnt."
, ".$sale_cnt."
, '".$isnew."'
, '".$isbest."'
, '".$isrecom."'
, '".$ismain."'
, '".$locate."'
, '".$_SESSION['AUID']."'
, '".$sale_end_date."'
, now()
, '".$delivery_fee."'
)";

$rs=$mysqli->query($query) or die($mysqli->error);
$pid = $mysqli -> insert_id;
if($rs){

My attempt adding prepared stmt:

$query="INSERT INTO 제품
(name, cate, content, thumbnail, price, sale_price, sale_ratio, cnt, sale_cnt, isnew, isbest, isrecom, ismain, locate, userid, sale_end_date, reg_date, delivery_fee)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '".$_SESSION['AUID']."', ?, now(), ?);";

$stmt = $conn->prepare($query);
$stmt->bind_param("ssssdddiiiiiiisd", $name, $cate, $content, $thumbnail, $price, $sale_price, $sale_ratio, $cnt, 
$sale_cnt, $isnew, $isbest, $isrecom, $ismain, $locate, $sale_end_date, $delivery_fee);
$stmt->execute();

$rs=$conn->query($stmt) or die($conn->error);
$pid = $conn -> insert_id;

Then i get error :

Unknown column 'now' in 'field list' Unknown column 'admin' in 'field list'

admin is the session id for ".$_SESSION[‘AUID’]."
Why is it recognizing as column?

I added apostrophe around ".$_SESSION[‘AUID’]." and its working
but new error now haha

 Uncaught TypeError: mysqli::query(): Argument #1 ($query) must be of type string, mysqli_stmt

its referring to the first line below

$rs=$conn->query($stmt) or die($conn->error);
$pid = $conn -> insert_id;
if($rs){

How can I apply prepared statements UPDATING query below?

$sql = "UPDATE 제품사진 SET pid=$pid WHERE imgid in (".$file_table_id.")";
Tags:

3

Answers


  1. 0 
    $stmt->bindParam("'".$_SESSION['AUID']."'", $userid);
$stmt->bindParam("now()", $reg_date);

    The problem are in this two lines. define variable referanace in query as :now not like now(), and define as same :session variable referance in query.

    First Change This Query Referance parameter check last session and now.

    $query = "INSERT INTO 제품 (name, cate, content, thumbnail, price, sale_price, 
sale_ratio, cnt, sale_cnt, isnew, isbest, isrecom, ismain, locate, userid, 
sale_end_date, reg_date, delivery_fee) VALUES (:name, :cate, :content, 
:thumbnail, :price, :sale_price, :sale_ratio, :cnt, :sale_cnt, :isnew, :isbest, 
:isrecom, :ismain, :locate, ':session', :sale_end_date, :now , :delivery_fee);";

    Then Change The Parameters Binding Lines Which are bind session and now.

    $stmt->bindParam(":session", $userid);
$stmt->bindParam(":now", $reg_date);

    Now it will be run, if not run run after the changes then change stmt->execute() to
    stmt->getRawSql() take the sql and try to run on mysql server there you can troubleshoot in more efficient way.

    Login or Signup to reply.
  2. 0

    Looks like you’re mixing PDO and mysqli paradigms. ‘bindParam’ vs ‘bind_param’.

    Read more here: https://www.php.net/manual/en/mysqli-stmt.bind-param.php

    Try something like this:

    $query = "INSERT INTO 제품 
(name, cate, content, thumbnail, price, sale_price, sale_ratio, cnt, sale_cnt, isnew, isbest, isrecom, ismain, locate, userid, sale_end_date, reg_date, delivery_fee)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), ?)";
$stmt = $conn->prepare($query);

$stmt->bind_param('ssssdddiiiiiisisd', 
$name, $cate, $content, $thumbnail, $price, $sale_price, $sale_ratio, $cnt, $sale_cnt, $isnew, $isbest, $isrecom, $ismain, $locate, $userid, $sale_end_date, $delivery_fee
);
$stmt->execute();

I can't determine the column types.  You will have to do that.
From the link:
The first field is the map for column types.

i   corresponding variable has type int
d   corresponding variable has type float
s   corresponding variable has type string
b   corresponding variable is a blob and will be sent in packets

    Example update:

    I'm assuming because you used 'in' that the file_table_id may include a list of values. In that case, $file_table_id would be a string that you create that looks like:
        
"n,n,n,n" where n is whatever type $pid is.
    
Otherwise, the where would just be "where imgid = ?"
        
$sql = "UPDATE 제품사진 
SET pid = ?
WHERE imgid in (?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param("is", $pid, $file_table_id);
$stmt->execute();
    Login or Signup to reply.
  3. 0

    A prepared statement in PHP is a way to execute SQL queries safely by separating SQL code from user-provided data. This helps prevent SQL injection attacks and ensures the integrity of your database operations. To create a prepared statement for an INSERT query in PHP, you can use the PDO (PHP Data Objects) extension. Here’s an example of how to use prepared statements for inserting data into a database:

    // Database connection parameters
$host = 'your_database_host';
$dbname = 'your_database_name';
$username = 'your_database_username';
$password = 'your_database_password';

// Data to be inserted
$name = 'John Doe';
$email = '[email protected]';

try {
    // Create a PDO connection
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);

    // Define the SQL query with placeholders
    $sql = "INSERT INTO your_table_name (name, email) VALUES (:name, :email)";

    // Prepare the SQL statement
    $stmt = $pdo->prepare($sql);

    // Bind the values to the placeholders
    $stmt->bindParam(':name', $name, PDO::PARAM_STR);
    $stmt->bindParam(':email', $email, PDO::PARAM_STR);

    // Execute the prepared statement
    $stmt->execute();

    // Optionally, you can check the number of affected rows
    $rowCount = $stmt->rowCount();

    if ($rowCount > 0) {
        echo "Data inserted successfully. Rows affected: $rowCount";
    } else {
        echo "No rows affected.";
    }
} catch (PDOException $e) {
    // Handle database connection or query errors
    echo "Error: " . $e->getMessage();
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search