How to insert text and html format data into a MySQL database via PHP?

Question

I have a problem inserting data (text and HTML format) into a MySQL field LONGTXT. Here is the error

  public 'error' => string 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''&lt;p&gt;

Haec subinde Constantius audiens et quaedam referente Thalassio doc' at line 1' (length=226)

error and almost clear. I used all the functions of protection (encoding format, quote, disabling html...) I thought of another way, I created two functions dealing with commas, semicolons and slash. Here is the code of the function addslashes:

FUNCTION addText($txt)
    {
        IF(get_magic_quotes_gpc()==false)
                {
                RETURN utf8_encode(addslashes($txt));
                }else{
                RETURN utf8_encode($txt);
                }

    }

protect commas function :

FUNCTION protect_virgules($txt)
    {
        IF($txt!='')
        {
            $x = strlen($txt);
            $newTxt = '';
            FOR($i=0;$i<=$x;$i++)
            {
                    IF($txt[$i]==',' || $txt[$i] == ';')
                    {

                        $newTxt.= '\\'.$txt[$i];
                    }
                    else
                    {
                        $newTxt.=$txt[$i];
                    }
            }
            RETURN htmlentities($newTxt);               
        }
        else
        RETURN '0';
    }

insert php function :

 public function insert($table,$data){  
    
    $this->last_insert_id = NULL;

    $fields = "";
    $values = "";
    foreach($data as $fld => $val){
        $values .= trim($this -> escape($val)).",";
         $fields .= $fld.",";
         
    }
    $values = '"'.substr($values,0,strlen($values)-1).'"';
    $fields = '"'.substr($fields,0,strlen($fields)-1).'"';
    $tab=array($this->escape($table),$fields,$values,"@last_id");
    $this->CALL_SP("sp_insert",$tab);
    if ( $result = mysqli_query( $this->linkId, "SELECT @last_id AS last_inserted_id" ) ) {
        while ($row = mysqli_fetch_assoc($result))
        {
            $this->last_insert_id = $row['last_inserted_id'];
        }
    }

    return  $this->queryId;
}

insert SQL proc code:

     BEGIN
 SET @stmt_sql=CONCAT("INSERT INTO ", tableName, "  (",fields,")VALUES(", param ,")");
 PREPARE stmt FROM @stmt_sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 SELECT LAST_INSERT_ID() into last_id;
END

Syntax error always grabs me by the throat. Could you help me please?

Also, try passing your data through mysqli_real_escape_string(). — Anirudh Ramanathan
– Anirudh Ramanathan, Commented Nov 1, 2012 at 14:38
the function escape in php function ($values .= trim($this -> escape($val)... return string after using mysqli_real_escape_string() — Aisthesis Cronos
– Aisthesis Cronos, Commented Nov 1, 2012 at 14:56

deceze · Accepted Answer · 2012-11-01 15:28:42Z

3

Don't utf8_encode, unless you want to convert strings from Latin-1 to UTF-8.
Don't use addslashes or depend on magic quotes; turn magic quotes off or use stripslashes to reverse their effects should you not be able to turn them off.
Don't manually replace and escape single characters, unless you have a very specific reason to.

Do escape once using the appropriate escaping mechanism for your database. If you're using mysql_* (don't use that anymore), use mysql_real_escape_string. If you're using mysqli_*, use ~~mysqli_real_escape_string or better yet~~ prepared statements. If you're using PDO, use prepared statements.

See The Great Escapism (What you need to know to work with text within text) for a longer, more detailed discussion of the topic.

Your current "prepared statement" is useless, since it does not separate the query from the values at all. You're just concatenating all values as usual, then force them through a prepared statement in one go. There's also no need for a stored procedure, this can all be better done using the client-side API.

So:

Disable magic quotes as explained in the manual.
Use prepared statements as explained in the manual.
Use mysqli::insert_id to get the last insert id as explained in the manual.
There is no 4.

edited Nov 1, 2012 at 15:28

answered Nov 1, 2012 at 14:53

deceze♦

525k89 gold badges806 silver badges954 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

deceze Over a year ago

Yes, PDO::quote is the _real_escape_string equivalent for PDO. I would always suggest prepared statements instead of escaping though. You'd need a really good reason to use any escaping function if you have access to prepared statements.

Aisthesis Cronos Over a year ago

first.. Thanks you for your reply. deceze, i will try your recommendation .

tadman Over a year ago

You should never have to use mysqli_real_escape_string if you're using mysqli correctly. To even suggest it is misleading. Prepared statements are the most reliable way to ensure your data is properly escaped. Manual escaping has no business being in general application code.

tadman Over a year ago

Stop using this classic method. You're going to hurt yourself. Just DO NOT. Placeholders are essential. They are not to be ignored. Your example here is impenetrably complex and would be very difficult to audit. PDO provides a straight-forward way for expressing the same thing in fewer lines of code.

deceze Over a year ago

@AisthesisCronos You can use real prepared statements even with mysqli, even with procedural mysqli. Just do it.

|

Collectives™ on Stack Overflow

How to insert text and html format data into a MySQL database via PHP?

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related