Special Characters in a database

[erockguide]

How do you prevent users from using special characters when they are inputing data into MYsql? For Example, on my page, users can add there own information, including an area name, how to get there, etc. I tried it myself and when I use the plus or the minus sign, the script won't work correctly.

I get the place name to display, but when I click it to see the rest of the information, there is nothing displayed. I am also prevented from deleting this row from the database. My script works fine on everything else, but those special characters really screw it up. Any ideas how I can fix this bug?

[LexF]

There are a couple things you can try.

One route would be to make sure that the field in your table is defined as "TEXT" -- MySQL allows any sort of characters in a TEXT field, versus a VARCHAR field.

Then, if there are still problem characters, use PHP's str_replace() function.

[erockguide]

Well, I tried the text way, and still doesn't work. What's happening is this: All of the info is most likely getting into the database. On the main page, I only call the Name (which may or may not contain a plus or minus sign) with a link to see the rest of the information. If there's a plus sign, the rest of the informatin will not appear. I wrote a parser to look for a + and a - sign, but it only works for the - sign. The plus sign can still be used, but creates the problem of not being able to call the other info in the row. Changing the name to Text didn't help. I don't know why a + sign causes such problems. I'm not sure what the other command you mentioned is. I'll have to dig out the PHP book for that one.

[HalfaBee]

How about posting a snippet of your code that is not working.
A + sign should not cause problems.

HalfaBee

[erockguide]

<?php

if ((ereg("-",$RName)) || (ereg("+",$RName)))
{
echo "Sorry, You can't use a minus or a plus symbol";
}
else
{


Everything below the last { symbol is the code used to put the data into the row, which is does. However, when I go to the main.php page, the RName is there with the link to see the rest of the data. Upon clicking the link, I get a blank page. This only happens when I use the + sign. In the above snippet, if I use a - sign, the echo is performed, but not with the + sign. The reason I'm concerned is becuase someone could input dirty words and things with a + sign, and I can't delete the row. My delete.php programs works on everything but the RNames with a + sign.

I can post more code if needed.

[HalfaBee]

the + doesn't work in ereg because it is a special character.

use \+ instead of +

It doesn't explain why you can't use a + sign in your script.
How about the code for where you create the link.

You should also pass the string thru htmlentities() to stop html being added to the data.

HalfaBee

[erockguide]

Well Halfabee, you are genius. The slash worked. I don't understand why the plus sign causes such problems. I just found the issue on accident. So far, nothing else causes problems but that seems to. Thanks for the assistance.

[erockguide]

Here's that code you asked about

//Attempt to select from Routes Table here

$sqlquery = "SELECT RName FROM Routs ORDER by RName";

$queryresult = mysql_query($sqlquery);





echo "<table border=0 align=center width=400 cellspacing=10 cellpadding=0>";

echo " <tr> ";
echo " <td width=200><center><span class=smallblackbold><font color=#9A451D><U><b>Route Name</B></U></font></center></td>\n";
echo "</tr>\n";


while ($row=mysql_fetch_array($queryresult))
{
echo " <tr>\n";
echo " <td><span class=smallblackbold>".$row["RName"]."</span></td>\n";
echo " <td><a href=\"display.php?RName=".$row["RName"]."\"><font color=#9A451D><span class=smallblackbold>View</a></font></span></td>\n";
echo " </tr>\n";


I call RName, and next to it have a link for dispaly.php, which is where the program calls the rest of the data input by the user. The plus sign seems to make the rest of the data disapear.

[HalfaBee]

The + sign is a special character in URLS as well.
If want to use these you will have to use %2B ( think this is correct ).

HalfaBee ( blushing from the compliment :) )

[erockguide]

Well, I've created another table and found that If I use quotes (Example: "something like this") for the Name field, upon linking to the rest of the info, it won't show up and I can delete the info either.

Here's what I've got: A folder called Articles. In it are these programs: addrow.php, display.php, main.php, udate.php, verify.php. When a user wants to add info the database, they click a link that takes them to verify.php. In there are three textarea boxes, one for title, author, and content. When they hit Submit, it goes to addrow.php, which inserts the data into the database, and echos the information input by the user. At this point, the user goes back to main.php, where the recently input data is shown. Main.php only shows the Title of each article, with a link to display.php that shows the title, author, and content for that article.

If, in the title, the user uses quotes (or a plus symbol), then on main.php, the title is shown, but when you click the link, there is nothing. The quotes also prevent me from deleting the bunk article. I have parsed the information in addrow.php to check and error if there is a plus, minus, or quote symbol.

Why are these characters causing such issues. I have changed the datatype from varchar, to text, to blob. I've taken the Primary Key off, put it on, taken it off again. I don't understand.

[HalfaBee]

You need to use the addslashes() function to escape the " or ' in the db.

You probably need a primary key that auto-increments and use this to reference the article not the text in it.

So your url looks like display.php?row=1234

HalfaBee