stripslashes() and MySQL

#1 (**permalink**) 02-25-2008

What is the proper way to use of the following functions:
mysql_real_escape_string() and stripslashes()?

Typically, I will use mysql_real_escape_string() when inserting a value
into the database, and I use stripslashes() when I pull in out. This
usually works, however, I run into trouble when I run an INSERT and
SELECT in the same PHP file.

For example, if I submit a form value of "Tester's Choice", it prints
back "Tester\\\'s Choice".

Any help would be appreciated.

Note that the file "edit_option.php" is calling itself with the form
action tag. Here is my code:

<?php
session_start();

if(@$_SESSION['admin'] != 1) {
header("location: login.php");
exit();
}

$message = '';
include '../includes/config.php';
include '../includes/connect.php';

if (isset($_POST['submit'])) {

$option_name = mysql_real_escape_string($_POST['option_name']);

if ($_POST['current_id']) {
$id = mysql_real_escape_string($_POST['current_id']);
$sql = "UPDATE `certificate_option` SET option_name=\"$option_name\"
WHERE option_id=\"$id\"";
mysql_query($sql) or die(mysql_error());
$message .= 'Option has been updated.';

} else {
$sql = "INSERT INTO `certificate_option` (option_name) values
(\"$option_name\")";
mysql_query($sql) or die(mysql_error());
$message .= 'Option has been saved.';
}

} else {
if (isset($_GET['id'])) {
$id = $_GET['id'];
}
}

$q = mysql_query("SELECT * FROM certificate_option WHERE
option_id=\"$id\"");
$count = 0;
while($row = mysql_fetch_array($q)) {
$option_id = $row['option_id'];
$option_name = stripslashes($row['option_name']);
}

mysql_free_result($q);
mysql_close($conn);

?>
<html>
<head>
<title>Admin Panel</title>
</head>

<body>

<?php include '../includes/admin_header.html'; ?>

<h1>Certificate Option Edit</h1>
<p><font color="#339933"><b><?=$message?></b></font></p>

<form action="edit_option.php" method="post">
<input type="hidden" name="current_id" value="<?=$id?>">

Option:<br>
<input type="text" maxlength="96" size="25" name="option_name"
value="<?=$option_name?>"><br><br>

<input type="submit" name="submit" value="Update Record">
</form>

<a href="certificate_option_list.php">Certificate Option List</a>

</body>
</html>

#2 (**permalink**) 02-25-2008

vol30w60 wrote:
> What is the proper way to use of the following functions:
> mysql_real_escape_string() and stripslashes()?
>
> Typically, I will use mysql_real_escape_string() when inserting a value
> into the database, and I use stripslashes() when I pull in out. This
> usually works, however, I run into trouble when I run an INSERT and
> SELECT in the same PHP file.
>
> For example, if I submit a form value of "Tester's Choice", it prints
> back "Tester\\\'s Choice".
>
> Any help would be appreciated.
>
> Note that the file "edit_option.php" is calling itself with the form
> action tag. Here is my code:
>
>
> <?php
> session_start();
>
> if(@$_SESSION['admin'] != 1) {
> header("location: login.php");
> exit();
> }
>
> $message = '';
> include '../includes/config.php';
> include '../includes/connect.php';
>
> if (isset($_POST['submit'])) {
>
> $option_name = mysql_real_escape_string($_POST['option_name']);
>
> if ($_POST['current_id']) {
> $id = mysql_real_escape_string($_POST['current_id']);
> $sql = "UPDATE `certificate_option` SET
> option_name=\"$option_name\" WHERE option_id=\"$id\"";
> mysql_query($sql) or die(mysql_error());
> $message .= 'Option has been updated.';
>
> } else {
> $sql = "INSERT INTO `certificate_option` (option_name) values
> (\"$option_name\")";
> mysql_query($sql) or die(mysql_error());
> $message .= 'Option has been saved.';
> }
>
> } else {
> if (isset($_GET['id'])) {
> $id = $_GET['id'];
> }
> }
>
>
> $q = mysql_query("SELECT * FROM certificate_option WHERE
> option_id=\"$id\"");
> $count = 0;
> while($row = mysql_fetch_array($q)) {
> $option_id = $row['option_id'];
> $option_name = stripslashes($row['option_name']);
> }
>
> mysql_free_result($q);
> mysql_close($conn);
>
>
> ?>
> <html>
> <head>
> <title>Admin Panel</title>
> </head>
>
> <body>
>
> <?php include '../includes/admin_header.html'; ?>
>
> <h1>Certificate Option Edit</h1>
> <p><font color="#339933"><b><?=$message?></b></font></p>
>
> <form action="edit_option.php" method="post">
> <input type="hidden" name="current_id" value="<?=$id?>">
>
> Option:<br>
> <input type="text" maxlength="96" size="25" name="option_name"
> value="<?=$option_name?>"><br><br>
>
> <input type="submit" name="submit" value="Update Record">
> </form>
>
> <a href="certificate_option_list.php">Certificate Option List</a>
>
> </body>
> </html>
>

You should not need to use stripslashes() when using
mysql_real_escape_string(). If you do, it means either you have used
addslashes() or have magic_quotes_gpc on.

If you used addslashes(), don't. It's not required. If you have
magic_quotes_gpc on, turn it off. Or, if you can't turn it off, call
stripslashes() on your data before you put it in the database.

If this is your entire code, it looks like the later is your problem.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

#3 (**permalink**) 02-25-2008

Jerry Stuckle wrote:
> vol30w60 wrote:
>> What is the proper way to use of the following functions:
>> mysql_real_escape_string() and stripslashes()?
>>
>> Typically, I will use mysql_real_escape_string() when inserting a
>> value into the database, and I use stripslashes() when I pull in out.
>> This usually works, however, I run into trouble when I run an INSERT
>> and SELECT in the same PHP file.
>>
>> For example, if I submit a form value of "Tester's Choice", it prints
>> back "Tester\\\'s Choice".
>>
>> Any help would be appreciated.
>>
>>
>
> You should not need to use stripslashes() when using
> mysql_real_escape_string(). If you do, it means either you have used
> addslashes() or have magic_quotes_gpc on.
>
> If you used addslashes(), don't. It's not required. If you have
> magic_quotes_gpc on, turn it off. Or, if you can't turn it off, call
> stripslashes() on your data before you put it in the database.
>
> If this is your entire code, it looks like the later is your problem.
>

Thanks! Turning off magic_quotes_gpc did the trick.

#4 (**permalink**) 02-26-2008

Not trying to re-scope your question, but PDO can also offer a lot of
great options in this area. I've found that using PDO has reduced the
amount of crazy data policework I have to do.

Just a suggestion! I realize it's a slight shift in the paradigms
used, but I've so far enjoyed the options it opens up.

Take care.

On Feb 25, 1:48 pm, vol30w60 <vol30...@yahoo.com> wrote:
> Jerry Stuckle wrote:
> > vol30w60 wrote:
> >> What is the proper way to use of the following functions:
> >> mysql_real_escape_string() and stripslashes()?
>
> >> Typically, I will use mysql_real_escape_string() when inserting a
> >> value into the database, and I use stripslashes() when I pull in out.
> >> This usually works, however, I run into trouble when I run an INSERT
> >> and SELECT in the same PHP file.
>
> >> For example, if I submit a form value of "Tester's Choice", it prints
> >> back "Tester\\\'s Choice".
>
> >> Any help would be appreciated.
>
> > You should not need to use stripslashes() when using
> > mysql_real_escape_string(). If you do, it means either you have used
> > addslashes() or have magic_quotes_gpc on.
>
> > If you used addslashes(), don't. It's not required. If you have
> > magic_quotes_gpc on, turn it off. Or, if you can't turn it off, call
> > stripslashes() on your data before you put it in the database.
>
> > If this is your entire code, it looks like the later is your problem.
>
> Thanks! Turning off magic_quotes_gpc did the trick.

#5 (**permalink**) 02-26-2008

vol30w60 wrote:
>
[snip]
>
> } else {
> if (isset($_GET['id'])) {
> $id = $_GET['id'];
> }
> }
>
> $q = mysql_query("SELECT * FROM certificate_option WHERE
> option_id=\"$id\"");
>
[snip]

Not a reply to your original question but you don't appear to be
validating/escaping/intval'ing $_GET['id'].

Robin

#6 (**permalink**) 02-27-2008

Omega wrote:
> Not trying to re-scope your question, but PDO can also offer a lot of
> great options in this area. I've found that using PDO has reduced the
> amount of crazy data policework I have to do.
>
> Just a suggestion! I realize it's a slight shift in the paradigms
> used, but I've so far enjoyed the options it opens up.
>

any example please? I now started using PDO....

#7 (**permalink**) 02-27-2008

On Wed, 27 Feb 2008 11:36:43 +0100, Harris Kosmidhs
<hkosmidi@remove.me.softnet.tuc.gr> wrote:

> Omega wrote:
>> Not trying to re-scope your question, but PDO can also offer a lot of
>> great options in this area. I've found that using PDO has reduced the
>> amount of crazy data policework I have to do.
>> Just a suggestion! I realize it's a slight shift in the paradigms
>> used, but I've so far enjoyed the options it opens up.
>
> any example please? I now started using PDO....

Prepared statemenst will make your live definitly easy:

$db = new PDO();//use some real connection variables.
$stmt = $db->prepare('SELECT foo FROM bar WHERE foz = ?');
$stmt->bindValue(1,"some'string\\with''\'characters that could be
escaped", PDO::PARAM_STR);
$stmt->execute();
var_dump($stmt->fetchAll());

Prepared Statement > mysql_real_escape_string > mysql_escape_string >
addslashes
--
Rik Wasmus

#8 (**permalink**) 02-27-2008

Rik Wasmus wrote:
> On Wed, 27 Feb 2008 11:36:43 +0100, Harris Kosmidhs
> <hkosmidi@remove.me.softnet.tuc.gr> wrote:
>
>> Omega wrote:
>>> Not trying to re-scope your question, but PDO can also offer a lot of
>>> great options in this area. I've found that using PDO has reduced the
>>> amount of crazy data policework I have to do.
>>> Just a suggestion! I realize it's a slight shift in the paradigms
>>> used, but I've so far enjoyed the options it opens up.
>>
>> any example please? I now started using PDO....
>
> Prepared statemenst will make your live definitly easy:
>
> $db = new PDO();//use some real connection variables.
> $stmt = $db->prepare('SELECT foo FROM bar WHERE foz = ?');
> $stmt->bindValue(1,"some'string\\with''\'characters that could be
> escaped", PDO::PARAM_STR);
> $stmt->execute();
> var_dump($stmt->fetchAll());
>
> Prepared Statement > mysql_real_escape_string > mysql_escape_string >
> addslashes

Sorry don't quite follow...

bindValue does something like mysql_real_escape_string?
Does it understand what foz is? integer, varchar, etc?

#9 (**permalink**) 02-27-2008

..oO(Harris Kosmidhs)

>Rik Wasmus wrote:
>
>> Prepared statemenst will make your live definitly easy:
>>
>> $db = new PDO();//use some real connection variables.
>> $stmt = $db->prepare('SELECT foo FROM bar WHERE foz = ?');
>> $stmt->bindValue(1,"some'string\\with''\'characters that could be
>> escaped", PDO::PARAM_STR);
>> $stmt->execute();
>> var_dump($stmt->fetchAll());
>>
>> Prepared Statement > mysql_real_escape_string > mysql_escape_string >
>> addslashes
>
>Sorry don't quite follow...
>
>bindValue does something like mysql_real_escape_string?
>Does it understand what foz is? integer, varchar, etc?

Yes, if you tell it so. The third parameter of bindValue() or
bindParam() can be used to define the type, which is PDO::PARAM_STR in
the example above. The DB will then take the appropriate actions to
handle the data properly. If you say "this is a string", then the DB
will take it as exactly that and will make sure that all special chars
will automatically be escaped if necessary.

Micha

#10 (**permalink**) 02-27-2008

On Wed, 27 Feb 2008 15:35:29 +0100, Michael Fesser <netizen@gmx.de> wrote:

> .oO(Harris Kosmidhs)
>
>> Rik Wasmus wrote:
>>
>>> Prepared statemenst will make your live definitly easy:
>>>
>>> $db = new PDO();//use some real connection variables.
>>> $stmt = $db->prepare('SELECT foo FROM bar WHERE foz = ?');
>>> $stmt->bindValue(1,"some'string\\with''\'characters that could be
>>> escaped", PDO::PARAM_STR);
>>> $stmt->execute();
>>> var_dump($stmt->fetchAll());
>>>
>>> Prepared Statement > mysql_real_escape_string > mysql_escape_string >
>>> addslashes
>>
>> Sorry don't quite follow...
>>
>> bindValue does something like mysql_real_escape_string?
>> Does it understand what foz is? integer, varchar, etc?
>
> Yes, if you tell it so. The third parameter of bindValue() or
> bindParam() can be used to define the type, which is PDO::PARAM_STR in
> the example above. The DB will then take the appropriate actions to
> handle the data properly. If you say "this is a string", then the DB
> will take it as exactly that and will make sure that all special chars
> will automatically be escaped if necessary.

Indeed, and to clarify: bindValue() does nothing to the string, nor does
PHP actually... It's the database that does it. See
<http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html>
for a quick introduction.
--
Rik Wasmus

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode