mysql escaped string storage

Could somebody explain me how to store a escaped string in a column limited in size of a mysql t开发者_Go百科able. I mean, if I have a column to which I define a size (let's say varchar(10)), if I insert "abcdefghij" the limit is reached, but if I insert "abcde'ghij" I exceed the limit since the escaped result of this last string is "abcde\'ghij" although the original size is 10! How to control this?

There's often a lot of confusion around string escaping and how the escape characters work. For example, given this PHP code:

$string = "My \"string\" is cool.";

The variable $string does not contain any slashes. The code I wrote there has some slashes in it, but they exist only for the benefit of the parser, to tell it that the following quote mark shouldn't end the string.

The same applies for databases, but this is where a lot of people trip up, because you often build a string which is read by 2 parsers, firstly your programming language (eg: PHP), and then by the database. For example, to insert the string "" (two quote marks, not an empty string) into the database, you'd need some code which looked like this:

$sql = "INSERT INTO myTable VALUES (\"\\\"\\\"\")";

PHP reads this and interprets the string to be this:

INSERT INTO myTable VALUES ("\"\"")

And then MySQL reads that and interprets the insert value as:

""

(two quotes)

Now that that is all sorted, what about the problem at hand? You say you have an escaped string? That's a problem. You should only escape a string as you need to, at the very last moment. If the data you want to store is 5 o'clock, then the value of the string should be 5 o'clock, not 5 o\'clock

When you are inserting that value into an SQL statement, that's the moment when you need to escape it.

$myString = '5 o\'clock';   // value is: 5 o'clock
$sql = sprintf(
    "INSERT INTO myTable VALUES ('%s')",
    mysql_real_escape_string($myString)   // 5 o\'clock
);
echo $sql; // INSERT INTO myTable VALUES ('5 o\'clock');

The ANSI SQL escape character is ', however MySQL also allows \ escaping if NO_BACKSLASH_ESCAPES isn't enabled.

Escaping a char doesn't actually take up one more char. It is only so the parser reads the input correctly.

For instance, inserting '1234567890' or '12345678''0' will both take up equals amounts of space and will not overflow the field bound.

The MySQL manual has a pretty complete chapter on this.

The value stored shouldn't include the backslashes. You are somehow escaping your data twice, as a comment stated (above).

very simplistic pseudocode, of how the process should look like:

data = "Let's party!"
data = escape(data) # Let\'s party
save(data)
## The database ignores the single quote, since it is escaped with a backslash
## but then removes the slash before storing data

read(data) # Let's party

You either have to allocate twice the space for values (assume that there's a potential that every character would need to escaped), or skirt the issue by escaping the text when emitting it, not when storing it.

(the disadvantage to the second one being when you're writing more often than you're reading; but the advantage is that you can deal with having to escape things differently depending on how or where it's being displayed).

update : I know that mysql won't store extra characters used for inserting, but it also doesn't use \' for an escaped single quote when inserting -- so I can only assume that the escaping is for whatever is consuming or otherwise making use of the values that they're storing. (eg, I've seen people store ' or " because the values are being used in HTML ... I'd rather take the hit and encode on emitting if there's a chance of the data being used for non-HTML purposes.)

when placing your data for storage, and not using prepared queries, you need to escape your data exactly once as denoted above.

When using prepared queries for the string literal portion of the query, you should not escape your data and doing so will result in your issue.

$db = new mysqli('localhost', 'my_user', 'my_password', 'world');
$sql = 'INSERT INTO `blah` (field1,field2) VALUES (?,?)';
$query = $db->prepare($sql);
$query->bind_parms('ss',$no_escaping,$needed_here);
$query->execute();

EDIT: Are you using prepared queries?

If you are, I've cobbled together a class for this here..