Single quotes in MSSQL

[nick.bonadies]

I'm trying to deal with user inputs of single quotes into form fields
that get input into a MSSQL database. So far I have discovered that
if I turn on magic_quotes_sybase in my php.ini file PHP will correctly
escape the single quotes. The problem happens when I am trying to
retrieve data from the database, PHP will try to comment out what it
has already commented out, instead of stripping the extra single
quote.

So as an example, if someone enters O'Brien as their name into the
form PHP send O''Brien to the database to be stored. Now when I have
say, a list of users in the database on another page PHP outputs
O''''Brien because it is trying to compensate for the single quotes.

Is there any sort of function like stripslashes(); but for single
quotes?

Thanks so much for any and all help! This is driving me up the wall!

[Toby A Inkster]

nick.bonadies wrote:

I'm trying to deal with user inputs of single quotes into form fields
that get input into a MSSQL database. So far I have discovered that
if I turn on magic_quotes_sybase in my php.ini file PHP will correctly
escape the single quotes.

Argh! Don't do that!

Just use str_replace("'", "''", $data) on data before you send it to the
database and don't do anything on the returned data.

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~ http://tobyinkster.co.uk/contact
Geek of ~ HTML/SQL/Perl/PHP/Python*/Apache/Linux

* = I'm getting there!

[nick.bonadies]

On Apr 11, 10:45 am, Toby A Inkster <[e-mail]>
wrote:

nick.bonadies wrote:

I'm trying to deal with user inputs of single quotes into form fields
that get input into a MSSQL database. So far I have discovered that
if I turn on magic_quotes_sybase in my php.ini file PHP will correctly
escape the single quotes.

Argh! Don't do that!

Just use str_replace("'", "''", $data) on data before you send it to the
database and don't do anything on the returned data.

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~http://tobyinkster.co.uk/contact
Geek of ~ HTML/SQL/Perl/PHP/Python*/Apache/Linux

* = I'm getting there!

yeah that makes much more sense! thank you!

nick

[nick.bonadies]

On Apr 11, 10:45 am, Toby A Inkster <[e-mail]>
wrote:

nick.bonadies wrote:

I'm trying to deal with user inputs of single quotes into form fields
that get input into a MSSQL database. So far I have discovered that
if I turn on magic_quotes_sybase in my php.ini file PHP will correctly
escape the single quotes.

Argh! Don't do that!

Just use str_replace("'", "''", $data) on data before you send it to the
database and don't do anything on the returned data.

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~http://tobyinkster.co.uk/contact
Geek of ~ HTML/SQL/Perl/PHP/Python*/Apache/Linux

* = I'm getting there!

Hmm actually, one problem, it works for inputting data but when i get
data back i still get a double single quote, so again my view still
looks like O''Brien, instead of O'Brien. should i just run
str_replace(); on the view? or is there an easier solution?

[Toby A Inkster]

nick.bonadies wrote:

Hmm actually, one problem, it works for inputting data but when i get
data back i still get a double single quote, so again my view still
looks like O''Brien, instead of O'Brien.

This means that the actual data in your database has two apostrophes. Fix
that and you'll be sorted.

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~ http://tobyinkster.co.uk/contact
Geek of ~ HTML/SQL/Perl/PHP/Python*/Apache/Linux

* = I'm getting there!

[nick.bonadies]

Toby A Inkster wrote:

>
This means that the actual data in your database has two apostrophes. Fix
that and you'll be sorted.

Correct, but when I use str_replace("'", "''", $data); to replace
single quotes in my data, it adds the extra quote to the database
entry.... i guess i'm doing something very wrong... thanks so much
for your help thus far!

[nick.bonadies]

Toby A Inkster wrote:

>
This means that the actual data in your database has two apostrophes. Fix
that and you'll be sorted.

Here is some sample code:

$queryAddRecord = "INSERT INTO tbl_employees(firstName, lastName)
VALUES(";
if (!empty($_POST['firstName']))
{
$queryAddRecord .= "'".$_POST['firstName']."',";
}
else {$queryAddRecord .="Null,";}

if (!empty($_POST['lastName']))
{
$queryAddRecord .= '"'.str_replace("'", "''",
$_POST['lastName']).'",';
}
else {$queryAddRecord .="Null,";}
$dbresults = mssql_query($queryAddRecord);

So if you feed that Erin O'Brien, it inputs Erin for the first name
and O''Brien as the last name. Then when i call it back i use
something like this:

<?php
$queryFullTimeemployees = "SELECT firstName, lastName FROM
tbl_employees ORDER BY lastName ASC";
$dbFullTime = mssql_query($queryFullTimeemployees);
?>

then display the records:

<table>
<?php
// List the departments from the DB
//display the results
while($row = mssql_fetch_array($dbFullTime)){
$pageAddress = "employees_edit.php?id=".trim($row['id']);
echo "<tr>
<td>".trim($row['lastName'])."</td>
<td>".trim($row['firstName'])."</td>
</tr>" ;
}
?>
</table>

[nick.bonadies]

$queryAddRecord = "INSERT INTO tbl_employees(firstName, lastName)
VALUES(";
if (!empty($_POST['firstName']))
{
$queryAddRecord .= "'".$_POST['firstName']."',";
}
else {$queryAddRecord .="Null,";}

if (!empty($_POST['lastName']))
{
$queryAddRecord .= '"'.str_replace("'", "''",
$_POST['lastName']).'",';
}

I figured it out. Its the '"' part, it should be "'".str_replace....

Thanks for the help! I appreciate it.