PHP mysqli bind_param type for text

Question 1

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

Question 2

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.

Question 3

I think you need more 's'-es in this?

 $stmt->bind_param("ss", $mtcn, $amount, $currency, $sender_name, $sender_country, $receiver_name, $comment, $support, $email);

try this (asuming they're all strings)

 $stmt->bind_param("sssssssss", $mtcn, $amount, $currency, $sender_name, $sender_country, $receiver_name, $comment, $support, $email);

Question 4

Looking Back on this question now a year later I can see exactly what i did wrong. in the form i didn't give it any action, i left it at <form action="" method="post">. It should have been <form action="#" method="post">. basic stuff up by a beginner.

Question 5

To be sure you see all PHP errors, add this code on top of your script:

error_reporting(E_ALL);
ini_set('display_errors', 1);

You must correct your calls to mysqli_real_escape_string. According to the documentation, there must be two parameters, and the first parameter must be a MySQL link. In your case that link would be $mysqli.

Also, replace:

if($row==1){

with:

if($result->num_row==1){

You are misunderstanding what $result->num_rows is: it contains the TOTAL number of rows returned by the query whose result is stored in $result. So, it is useless to check the value of $result->num_rows inside the loop where you retrieve all records returned by the query.

I removed the constant MYSQLI_USE_RESULT from your query() because the documentation for mysqli_query says:
If you use MYSQLI_USE_RESULT all subsequent calls will return error Commands out of sync unless you call mysqli_free_result().

New code:

<?php
    $mysqli = new mysqli('localhost', 'root', 'password', 'aiesec');

    /* check connection */
    if (mysqli_connect_errno()) {
        printf("Connect failed: %sn", mysqli_connect_error());
        exit();
    }

    // cleanup POST variables
    $myusername = mysqli_real_escape_string($mysqli, stripslashes(trim($_POST['myusername'])));
    $mypassword = mysqli_real_escape_string($mysqli, stripslashes(trim($_POST['mypassword'])));

    // If result matched $myusername and $mypassword, table row must be 1 row
    $sql = "SELECT * FROM members WHERE username='$myusername' and password='$mypassword'"; 
    $result = mysqli->query($sql);
     if($mysqli->errno<>0)
        die("Errormessage: %sn", $mysqli->error);
    echo $result->num_rows;
    if($result->num_rows==1){
        echo "correct username and pass";
        // Register $myusername, $mypassword and redirect to file "login_success.php"
       // session_register("myusername");
        //session_register("mypassword");
        //header("location:login_success.php");
    }
    else {
        echo "Wrong Username or Password";
    }
    mysqli_close();     
?>

Question 6

You need to take a look at the manual:

You should not escape your values when you use a prepared statement as you will be adding literal backslashes in your data.
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.

PHP mysqli bind_param type for text

Answers

php,mysqli