Escape Apostrophe in sql-php

[roosterbrood]

Good Evening All

 

I am fairly new to php and I know my question was asked before on different pages, but I tried to impliment the mysql_real_escape but could not manage to get it to work.

 

My problem: I used a youtube tutorial to write the script for a php-sql guestbook. It works fine except when a person use an apostrophe in his name or in the text area, when it gives an error.

 

My code:

 

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
        
            Name<br> <input style="width:400px" type="text" name="txt_name"> <br>
            Email:<br> <input style="width:400px" type="text" name="txt_email"> <br><br>
            Comment:<br>
            <textarea style="width:400px" rows="10" align="left" name="txt_comment">
            </textarea>
            <input type="submit" value="Send">
        </form>
        
        <h2>Other People's comments</h2>
        
        <table class="gb">
        
        <?php
            $query = "SELECT * FROM guestbook ORDER BY date_auto";
            $result = mysql_query($query, $connection);
            
            for ($i=0; $i < mysql_num_rows($result); $i++)
            {
            $name = mysql_result($result, $i, "name");
            $email = mysql_result($result, $i, "email");
            $comment= mysql_result($result, $i, "comment");
            $date = mysql_result($result, $i, "date_auto");
            $show_date = date("m/d/Y", $date);
                               
            
                echo '<br>';
                
                echo '
                    <tr height="45" bgcolor="#6B4226">
                        <td>
                        <font color="#fff">
                        ';
                            echo  $name ;
                            echo' van '.$email;
                            echo', ' .$show_date;
                    '</td>
                        
                    </tr>
                                    
                        ';    
                        
                echo '
                    <tr>
                        <td bgcolor="#FFCC99">';
                            echo $comment;
                        '</td>
                        
                    </tr>
                    
                    <br>
                    
                        ';        
                        
            }
        ?>
        </table>

 

I will apreciate it if someone can please point me in the right direction to successfully impliment mysql_real_escape or the newer version of it.

 

Thanks

André

 

 

[Irate]

Use code tags when posting code, please.

 

The escape function is called mysql_real_escape_string, and you should generally avoid usint mysql_* function because the native support it had will be removed in further distributions of PHP. It's already deprecated in older code.

Use mysqli or PDO instead, anyway.

[Ch0cu3r]

mysql_real_escape_string should be used when using user input in mysql queries. 

Example code

// connect to mysql
msyql_connect('host', 'user', 'pass');
mysql_select_db('databse');

// escape data
$name    = mysql_real_escape_string($_POST['name']);
$message = mysql_real_escape_string($_POST['message']);

// insert escaped data into table
mysql_query("INSERT INTO table (name, message)  VALUES ('$name', '$message')");

Use mysqli or PDO instead, anyway. 

And use prepared statements Edited October 21, 2013 by Ch0cu3r

[roosterbrood]

Thanks for the reply. I have read that I should use mysqli instead, but I don't know how to impliment it into my current code, can you please assist?

[Ch0cu3r]

Basically add an i in front of the mysql function prefix and in most cases pass in the connection resource as the first argument.

 

My example code using mysqli

$conn = mysqli_connect('localhost', 'root', 'pass', 'database');

$name    = mysqli_real_escape_string($conn, $_POST['name']);
$message = mysqli_real_escape_string($conn, $_POST['message']);

mysqli_query($conn, "INSERT INTO table (name, message)  VALUES ('$name', '$message')");

The same example using mysqli prepared statements

$mysqli = new mysqli('localhost', 'root', 'pass', 'database');

$stmt = $mysqli->prepare('INSERT INTO table (name, message) VALUES (?, ?)'); // pepare the query. Use placeholders for values
$stmt->bind_param('ss', $_POST['name'], $_POST['message']);                  // values to be used in queries (in order)
$stmt->execute();                                                            // execute the query

The documentation and examples are really easy to understand in the manual

http://php.net/mysqli

Edited October 22, 2013 by Ch0cu3r