best practice addslashes? column length

[chidge]

so if I use addslashes do I need to double my colum sizes?

so for instance I have

comments char(120),

and my php limits on 120 characters

so in my input I can only enter 60 quotes therefore (giving me 120 characters)

not that anyone is going to want to enter 60 quotes for the input but if they do..... it wont work. So should i double my table colum lengths?

is this worrying to much about it?

[Old Pedant]

Ummm...the slashes *SHOULD* only exist in the PHP code. They should *NOT* be stored in the DB. Period.

That is, if you do
$sql = "INSERT INTO table (field) VALUES('John\'s comment was \"Blech\"!')";

Then the string that ends up in the DB will be *ONLY*
John's comment was "Blech"!

No "doubled" characters, at all.

It would be a horrible mistake to *put* "escaped" characters into the database! For one thing, if you then tried to display that text in the brower, you'd have to write SPECIAL CODE to get rid of the slashes! The browser doesn't pay any special attention to them! And, too, what about people viewing the data *not* via PHP code?

Don't look at what the stuff looks like in PHP. View it directly in the DB to see what's really there.

[chidge]

ok thats totally confused me!

I have gpc on in php.ini and I am using prepared statments to enter my data into the db - this means the quotes in my database are appearing escaped and have forward slashes infront of them.

I then strip slashes on displaying items from the db in my pages.

and this is completely wrong?

*I will look into this in the morning I guess I must be double escaping something somewhere!


Ok looking around -

if GPC is on I need to stripslashes from my get, post and cookie data before I put it into the database

so for instance using this (I found in one of my books)



function nukeMagicQuotes() {
if (get_magic_quotes_gpc()) {

function stripslashes_deep($value) {
$value = is_array($value) ? array_map('stripslashes_deep', $value) : stripslashes($value);
return $value;
}

$_POST = array_map('stripslashes_deep', $_POST);
$_GET = array_map('stripslashes_deep', $_GET);
$_COOKIE = array_map('stripslashes_deep', $_COOKIE);
}
}



I apply this on any page that inputs data into the database the data is then striped of slashes? then I add it into the db. How do I know (apart from the fact that I put the function into my code) that the data is escaped?

massiveley confused - time for bed.

[Fumigator]

I agree with Ol' Pedant, don't store escape characters in your database. Use the function mysql_real_escape_string() to escape the input-- the data that goes into your table will not contain the escape characters but the data will be properly escaped at the point it is used in the query.

http://us.php.net/manual/en/function.mysql-real-escape-string.php

[chidge]

sorry dont get me wrong I also agree - I am just frustrated that what I have learnt is totally wrong (thank goodness I decided to post to check)

very confusing

[chidge]

if I add


$blogId = mysqli_real_escape_string($conn, $_POST["id"]);
$article = mysqli_real_escape_string($conn, $_POST["value"]);

$Insert = "INSERT INTO blog (blog_id, article)VALUES (?,?)";

$stmt = $conn->stmt_init();

if ($stmt->prepare($Insert)){
$stmt->bind_param("is", $blogId, $article);
$stmt->execute();
$stmt->store_result();
}


this then adds three slashes per quote actually in my Database!

My word it gets more confusing!!! :) (oh dear)

[chidge]

so I am escaping my data twice right?

this is actually escaping my data


$blogId = $_POST["id"];
$article = $_POST["value"];

$Insert = "INSERT INTO blog (blog_id, article)VALUES (?,?)";

$stmt = $conn->stmt_init();

if ($stmt->prepare($Insert)){
$stmt->bind_param("is", $blogId, $article);
$stmt->execute();
$stmt->store_result();
}

with no need to add a mysqli_real_escape_string.

ah yeah sorry forgot to mention I am using prepared statements! (just had a good read of this (http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html))

thanks guys...

[Old Pedant]

Correct. That's just one of the advantages of prepared statements. In addition, you avoid SQL Injection attacks.

[chidge]

One more thing.

So after that confusion I am using prepared statements or mysqli_real_escape_string to escape input heading in to my db.

Do I also need to add slashes if gpc is off?

so for instance is this needed to run all my inputs through before sending to my mysqli statements ?



$blah = clean($_POST['blah']);


//clean data going into the db
function clean($data){

if (!get_magic_quotes_gpc()) {
$data = addslashes($data);
}

return $data;
}


or do i not need to worry, if GPC is off then the prepared statemnts or escape string will escape the values anyway?

I am thinking I dont need to worry and infact after validating the $_POST in whatever way I need to I can pass it straight to my mysqli statements (after a trim) I just wanted to confirm before I correct this site wide with changes... as I have had much confusion over this (but now it all seems really quite clear)

Thanks in advance (Old Pedant you are a star)

[CFMaBiSmAd]

If magic_quotes_gpc() is ON, you actually need to remove the slashes that it added because magic_quotes_gpc() does not escape all the special characters that can break a query. This is why the magic_quotes settings are being removed in php6, it is inappropriate for a language to automatically manipulate ALL external data that might not even need to be escaped, especially if it doesn't do everything it should and you have to undo what it did and do it again the right way.

This will give you raw un-escaped data. If you are using prepared statements, just use the raw un-escaped data. If you are using a normal mysql(i)_query(), then you need to use the appropriate mysql(i)_real_escape_string() function on the data before you put it into the query string.