0

I would really appreciate any help I can get with this.

I am having real trouble with a form and I cant seem to get mysql_real_escape_string to work at all.

I am using MySQL 5.5 with PHP and to test it out I have created a simple form Magic Quotes is not turned on at all.

Using this form:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<form name="event_ad" action="test.php" method="post">
    <table>
    <tr>
        <td>Event Name:</td>
        <td><input name="event_name" type="text" size="90" /></td>
    </tr>
    <tr>
        <td>Start Date:</td>
        <td><input name="start_date" type="text" size="90" /></td>
    </tr>
    <tr>
        <td colspan="2" align="center"><input name="Submit" type="submit" id="Submit" value="Add New Event" /></td>
    </tr>
    </table>
</form>
</body>
</html>

Then the data is input nto the table using test.php

<?php
    mysql_connect("localhost","username","password") or die(mysql_error());
    mysql_select_db("databasename") or die(mysql_error());

    $name = $_POST['event_name'];
    $sdate = $_POST['start_date'];
    $name = mysql_real_escape_string($name);

    $sql = "INSERT INTO tblevents (event_name, event_date) VALUES ('" . $name . "','" . $sdate . "')";

    mysql_query($sql);

    echo "success";
?>

(connection details changed)

When I input the Event Name O'Rielly "Smith" it is inserted into the MySQL table as O'Rielly "Smith"

There is no back slashes, no escaping at all.

I really have scoured the internet trying to work this out but it seems that it works for everyone else.

Am I missunderstanding something fundamental here? I would thoroughly appreciate if someone could steer me in the right direction.

This is my first post on this forum, and the html and code formating dont seem to be the same as most forums I have visited, and you cant preview so I hope this turns out OK.

Thanx in advance to any who offer help. Cheers Al.

Improve this question
5
  • 7
    Forget mysql_real_escape_string. Use mysqli or PDO and bound arguments instead. Commented May 3, 2012 at 14:34
  • 2
    How do you know that is what it is doing? Are you echoing $name? Commented May 3, 2012 at 14:34
  • Sorry it doesn't answer your question, but I highly recommend switching to something along the lines of PDO. Mysql functions are being deprecated starting with PHP version 5.4. Commented May 3, 2012 at 14:35
  • Also If I use the following as an Event Name: <script>blah blah \n ""</script> it gets inserted straight into the table and I can see it exactly as that using phpMyAdmin to look at the table. I thought that was the sort of thing that mysql_real_escape_string was supposed to stop, by adding back slashes to it. Commented May 3, 2012 at 14:37
  • Please learn first what escaping is. It's best you know about what you use for what it is. Commented May 3, 2012 at 14:39

5 Answers 5

Reset to default
3

This is by-design, you shouldn't see any escaped input in your table. It's just for inserting. By using mysql_real_escape_string, your query looks like 

INSERT INTO tblevents (event_name, event_date) VALUES ('O\'Rielly \"Smith\"','1.4.2001')";

where these backslashes make sure you don't break the apostrophe

MySQL "deletes" all the backslashes until they're escaped by backslash :)

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

3 Comments

+1. The escaped data is for the query parser only. The whole point is to get real data into the table.
Thanx Martin. That is exactly the information I had been looking for, but funnily enough, I couldn't find that anywhere. I now have a much better understanding of how this works. Cheers.
If you like my answer the best, please accept it by ticking it on the left. Thanks
2

When I input the Event Name O'Rielly "Smith" it is inserted into the MySQL table as O'Rielly "Smith"

That is what is supposed to happen.

Escaping makes the data pass through the query so that the original data ends up in the database. The purpose is not to add extra characters to your stored data, it is to stop characters with special meaning from breaking your SQL.

Improve this answer

Comments

0

Think of escaping as the equivalent of wrapping paper on a birthday present.

You escape data so that any special characters in the data lose their special meaning during the transfer process. Once they're "inside" the target system (html page, database record, etc...), the escaping is no longer necessary, as the place where they could have affected the process is now over.

Same with the wrapped present - once the present is received, you don't need the wrapping paper anymore, so it's removed.

Improve this answer

Comments

0

It has worked fine.

Removing the mysql_real_escape_string will prevent the data being inserted.

Improve this answer

1 Comment

I am begining to understand now, but unfortunately, If I remove the mysql_real_escape_string the data still gets inserted into the table, no matter how out there the data is. This is why I am getting confused.
0 
$name = $_POST['event_name'];
$sdate = $_POST['start_date'];


$db = new mysqli("localhost", "username", "password", "databasename");

$query = "INSERT INTO tblevents (event_name, event_date) VALUES (?, ?)";
$statement = $db->prepare($query);
$statement->bind_param("ss", $name, $sdate);

$statement->execute();
$statement->close();

$db->close();

(I've left out error-checking for connecting)

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.