Asked  7 Months ago    Answers:  5   Viewed   77 times

For a feedback form that will dump user comments into a MySQL table, I'm unsure which bind_param type to use for the user-supplied feedback text (MySQL field type = text)

function sql_ins_feedback($dtcode,$custip,$name,$email,$subject,$feedback)
{
    global $mysqli ;
    if($stmt = $mysqli->prepare("INSERT INTO feedback (dtcode,custip,name,email,subject,feedback) VALUES (?,?,?,?,?,?)")) 
    {
        $stmt->bind_param("ssssss", $dtcode,$custip,$name,$email,$subject,$feedback);
        $stmt->execute() ;
        $stmt->close() ; 
    }
}

OR THIS?

        $stmt->bind_param("sssssb", $dtcode,$custip,$name,$email,$subject,$feedback);

So, is the blob type the correct bind_param type for a text field?

What is the size limit for a bind_param("s") type?

Is there anything else one must do when using bind_param("b") ? The manual (and something else I read somewhere/sometime) suggests blob types are treated differently -- anything I should know?

Thanks

 Answers

19

This actually depends on the Mysql server. The default max size for all data combined in the entire query is 1mb. See: http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

If your data combined is under that "max_allowed_packet" threshold, just use "s" for the binding type for any text field. Infact, you can usually get away with using "s" for any field type at all (date, float, etc).

If your entire entry combined that you want to insert is over 1mb (or whatever you reset it to) in length, you'll want to use mysqli_stmt::send_long_data method and the "b" binding type to send this particular field in chunks.

Wednesday, March 31, 2021
 
NewPHP
answered 7 Months ago
68

Use correct syntax:

$result = mysqli_query($con, "SELECT * FROM my_tablename");

You forgot to link current mysqli connection. First parameter is link - which mysqli connection you want to use (good for multiple conns) and then the second is your query.

Wednesday, March 31, 2021
 
seaders
answered 7 Months ago
24

Modification is:

$stmt->bind_param ( "ss", $user, $pass); because 1 data type is not defind in bind_param (). bind_param() will take two arguments 1st one is types (i, d, s, b) corresponding datatype in your query(?) and 2nd arg are values.

Suggestion's are:

  1. Don't compare with ==, for empty string because if user enter's 3 white spaces it will not equal. use empty() for checking empty string or not.

  2. Don't call unnecessary methods, it does not have any meaning, for eg: in your code your calling trim() after md5(). md5() will not return any white space character. So calling trim(md5($username)) is meaning less.

Try to replace your code with my code hope your problem is solved.

public function login($_username, $_password) {
$this->sessionOpen ();

 if (empty($_username)) {
    $this->log->error ( "Username vuoto" );
    throw new AuthLoginFailed ();
}
if (empty($_password)) {
    $this->log->error ( "Password vuota" );
    throw new AuthLoginFailed ();
}

$db = new mysqli ( $this->sql ['server'], $this->sql ['username'], $this->sql ['password'], $this->sql ['database'] );
if (mysqli_connect_errno ()) {
    $this->log->error ( "Errore di connessione a mysql: " . mysqli_error ( $db ) );
    throw new MysqliConnectionError ( "Mysqli error: " . mysqli_error ( $db ) );
}

$stmt = $db->prepare ( "SELECT id,org_id,org_group_id,people_id FROM users WHERE 'username' = ? AND 'password' = ?" );
if (! $stmt) {
    $this->log->error ( "Mysqli prepare error: " . mysqli_error ( $db ) );
    throw new MysqliPrepareException ( "Mysqli error: " . mysqli_error ( $db ) );
}
echo md5 ( $_username ) . "---" . md5 ( $_password );
//on page username and password is showed at this point
$user=md5 ( $_username );
$pass=md5 ( $_password );
$stmt->bind_param ( "ss",  $user,$pass);
/* Execute it */
$stmt->execute ();
if (! $stmt) {
    $this->log->error ( "Mysqli prepare error: " . mysqli_error ( $db ) );
    throw new MysqliExecuteException ( "Mysqli error: " . mysqli_error ( $db ) );
}


$stmt->fetch($rst);

echo "results: " . $rst->num_rows; //output of this: results:

if ($rst->num_rows == 0) {
    throw new AuthLoginFailed ();
}



/* Close statement */
$stmt->close ();

/* Close connection */
$db->close ();
}

Let me know once your problem is solved.

Saturday, May 29, 2021
 
EastSw
answered 5 Months ago
89

You need to take a look at the manual:

  1. You should not escape your values when you use a prepared statement as you will be adding literal backslashes in your data.
  2. You should not inject your variables in the query but use placeholders (question marks in mysqli) instead. These are bound to your values.

So your query would be:

$query = "INSERT INTO store_customers (
                name,
                email,
                // etc.
            ) VALUES (
                ?,
                ?,
                // etc.
            );
        ";

And you bind your values:

$stmt->bind_value(
    'sssssssissssss',
    $_POST['customer_name'],
    $_POST['customer_email'],
    // etc.
);

Note that I am using bind_value() instead of bind_param() as this seems to be used once only so there is no need to bind parameters, you can bind the values directly. It should not make a difference though.

Saturday, May 29, 2021
 
Gordnfreeman
answered 5 Months ago
36

This is how your code should look (with added SQL Injection protection):

<?php
include "dbinfo.php"; //contains mysqli_connect information (the $mysqli variable)
//inputs
$name = mysqli_real_escape_string($_GET['name']);
$text = mysqli_real_escape_string($_GET['text']);

$sqlqr = "INSERT INTO `ncool`.`coolbits_table` (`name`, `text`, `date`) VALUES ('" . $name . "', '" . $text . "', CURRENT_TIMESTAMP);";

mysqli_query($mysqli,$sqlqr); //function where the magic happens.
?>

Take a look at what I've done. Firstly I've escaped the user input you're retrieving into the $name and $text variables (this is pretty much a must for security reasons) and as others have suggested you should preferably be using prepared statements.

The problem is that you weren't surrounding string values with single quotes ('), which is a requirement of the SQL syntax.

I hope this helps to answer your question.

Thursday, September 2, 2021
 
Pachvarsh
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :