Jump to content


Photo

database adding stoppage


  • Please log in to reply
5 replies to this topic

#1 skideh

skideh
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 19 July 2006 - 05:50 PM

Hi. This is probably quite a simple problem, but I really can't think of what to type to find this in the manual without reading it all.
So i'll just try to explain as best as I can.

So I have a website, on this website is a secure area - which i log onto, and fill out a form to add news to a database. this worked fine on the previous version of php, but it has recently been upgraded and has caused all sorts of troubles.
firstly magic slashes was enabled - which caused annoying problems. so that was taken out of the config file.

anyway. i add the main news in a text field (including information from various other fields) and hit verify - it comes up with a page which displays what the post would look like - and all is fine. so i click submit - and the data is added to the database. every add's fine apart from if there is a "non-standard" chracter in the text field. for example - if i type in the post:

And it cost £10. I thought that was cheap.

what will be added to the database is:

And it cost

it does this with quotations too, possibly some other chracters. i have tried phasing out certian commands i run on the string - like htmlspecialchars and mysql_real_escape_string - but this has no effect on this problem.
the text field in the database uses utf8_general_ci collation. and it isnt a problem with the chracter limit.

if i use phpmyadmin to edit the database and add these characters it works fine - and the characters are added.

i think that is everything explained. i am hoping this is just an option in the config file.
if anyone can answer that'd be awesome.

thanks

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 19 July 2006 - 06:19 PM

Better post your code.

I tried putting that same text in a text field, submitting the form then writing to a db.

In my db I now have "And it cost £10. I thought that was cheap."
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 skideh

skideh
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 19 July 2006 - 06:39 PM

<?php
include('include.php');
connect();
function parsedate($value)
{
  $reformatted = preg_replace("/^\s*([0-9]{1,2})[\/\. -]+([0-9]{1,2})[\/\. -]+([0-9]{1,4})/", "\\2/\\1/\\3", $value);
  return strtotime($reformatted);
}

if (isset($_POST['submit']) or isset($_POST['verify']) or isset($_POST['back']))
{
  $date = parsedate($_POST['date']);
  $user = $_POST['user'];
  $title = $_POST['title'];
  $text = $_POST['text'];
  if ($text == "")
    $error = "Please enter some text";
  if ($user == "")
    $error = "Please select a user";
  if ($title == "")
    $error = "please enter a title";
  if ($date == -1)
    $error = "Please enter a valid date";
  if ($date == -1)
    $date = time();
  $text2 = output_post($text); //this definatly doesnt affect it. it only runs a few replaces (for bb code)
  if (isset($_POST['submit']) and !isset($error))
  {
    $query = 'INSERT INTO news (user, date, title, text) VALUES ('.$user.', FROM_UNIXTIME('.$date.'), \''.mysql_real_escape_string($title).'\', \''.mysql_real_escape_string($text2).'\')';
    if (!mysql_query($query))
      die('Invalid query: ' . mysql_error());
    else
      $host = $_SERVER['HTTP_HOST'];
      $uri = rtrim(dirname($_SERVER['PHP_SELF']), '/\\');
      header("Location: /..");
      exit();
  }
}

write_header('Add News');

if (isset($error))
  echo '<span class="error">Error: '.$error.'</span>';
if (isset($error) or !isset($_POST['verify']))
{
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
  <table>
    <tr>
      <td>Name:</td><td><select name="user">
<?php
$query = 'SELECT id, name FROM users';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
  if (isset($user) and ($line['id'] == $user))
    echo '<option value="'.$line['id'].'" selected="true">'.$line['name'].'</option>';
  else
    echo '<option value="'.$line['id'].'">'.$line['name'].'</option>';
}
?>
    </select></td>
    </tr>
    <tr>
      <td>Date:</td><td><input type="text" name="date" value="<?php
        if (isset($date))
          echo date('d\/m\/Y', $date);
        else
          echo date('d\/m\/Y');
      ?>" /></td>
    </tr>
    <tr>
      <td>Title:</td><td><input type="text" name="title" value="<?php if (isset($title)) {echo $title;} ?>" /></td>
    <tr>
      <td>Text:</td><td><textarea name="text" rows="20" cols="50"><?php if (isset($text)) { echo $text; } ?></textarea></td>
    </tr>
    <tr>
      <td colspan="2"><input type="submit" name="verify" value="Verify" /></td>
    </tr>
  </table>
</form>
<?php
}
else
{
$query = 'SELECT name FROM users WHERE id = '.$user;
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$line = mysql_fetch_array($result, MYSQL_ASSOC);
echo '<table class="news">';
echo '<tr><td class="newsheader">'.$title.'<img src="../'.$line['name'].'P.png" align="right" /></td> <td class="newsimage"><img align="right" style="display: inline;" src="../'.$line['name'].'.jpeg" /></td></tr>';
echo '<tr><td class="newsstory" colspan="2">'.str_replace("\n", '<br />', $text2).'</td></tr>';
echo '<tr><td class="newsdate" colspan="2">'.date('l jS \o\f F Y', $date).'</td></tr>';
echo '</table> <br />';
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
  <input type="hidden" name="user" value="<?php echo $user; ?>" />
  <input type="hidden" name="date" value="<?php echo date('r', $date); ?>" />
  <input type="hidden" name="title" value="<?php echo htmlspecialchars($title); ?>" />
  <input type="hidden" name="text" value="<?php echo htmlspecialchars($text); ?>" />
  <input type="submit" name="back" value="Back" />
  <input type="submit" name="submit" value="Submit" />
</form>
<?php
}
write_footer();
?>

this is function output_post
function output_post ($post) {
//Make safe any html
$post_no_html = htmlspecialchars($post);

//Make sure there is no whitespace at the end of the message
//It's conceivable that the user will start their message with whitespace
$post_abridged = chop($post_no_html);

//Callback function for preg_replace_callback below
        function convert_for_html ($matches) {
                $regex[0] = "[";
                $regex[1] = "]";
                $replace[0] = "[";
                $replace[1] = "]";
                ksort($regex);
                ksort($replace);
                $treated = str_replace($regex, $replace, $matches[1]);
                $output = '<table class="code"><tr><td>Code:</td></tr><tr><td class="code_box">' . $treated . '</td></tr></table>';
                return $output;
        }

        //Convert code tags
        $code_treated = preg_replace_callback("/\[code\](.+?)\[\/code\]/s","convert_for_html",$post_abridged);

        //Arrays for the bbCode replacements
        $bbcode_regex = array(0 => '/\[b\](.+?)\[\/b\]/s',
                                                1 => '/\[i\](.+?)\[\/i\]/s',
                                                2 => '/\[u\](.+?)\[\/u\]/s',
                                                3 => '/\[quote\](.+?)\[\/quote\]/s',
                                                4 => '/\[quote\=(.+?)](.+?)\[\/quote\]/s',
                                                5 => '/\[url\](.+?)\[\/url\]/s',
                                                6 => '/\[url\=(.+?)\](.+?)\[\/url\]/s',
                                                7 => '/\[img\](.+?)\[\/img\]/s',
                                                8 => '/\[color\=(.+?)\](.+?)\[\/color\]/s',
                                                9 => '/\[size\=(.+?)\](.+?)\[\/size\]/s');
        $bbcode_replace = array(0 => '<b>$1</b>',
                                                1 => '<i>$1</i>',
                                                2 => '<u>$1</u>',
                                                3 => '<table class="quote"><tr><td>Quote:</td></tr><tr><td class="quote_box">$1</td></tr></table>',
                                                4 => '<table class="quote"><tr><td>$1 said:</td></tr><tr><td class="quote_box">$2</td></tr></table>',
                                                5 => '<a href="$1">$1</a>',
                                                6 => '<a href="$1">$2</a>',
                                                7 => '<p align="center"><img src="$1" alt="[Image: $1]" title="User submitted image"/></p>',
                                                8 => '<span style="color:$1">$2</span>',
                                                9 => '<span style="font-size:$1pt">$2</span>');

        ksort($bbcode_regex);
        ksort($bbcode_replace);

        //preg_replace to convert all remaining bbCode tags
        $post_bbcode_treated = preg_replace($bbcode_regex, $bbcode_replace, $code_treated);

        return $post_bbcode_treated;
};

i hope this helps. it seems a bit of mess. if you need it splitting up somewhat more i'll do that.

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 19 July 2006 - 06:59 PM

This si the simple table I set up

CREATE TABLE `data` (
  `txt` varchar(150) default NULL,
  `adate` date default NULL,
  `title` varchar(20) default NULL
) TYPE=MyISAM

And this is the code that works
<?php 
include 'db.php';
if (isset($_GET['txt']) && !empty($_GET['txt'])) {
	$t = mysql_real_escape_string($_GET['txt']);
	mysql_query("INSERT INTO data VALUES ('$t', CURDATE(), 'aaaa' )") or die(mysql_error());
}

?>
<form>
       <input type="text" name="txt">
       <input type="submit" name="submit" value="submit">
</form>

I'm useless with regex, but if you want to try adding your extra bits to my basic code, one bit at a time, then it may help to pin down where things go wrong

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 skideh

skideh
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 19 July 2006 - 08:55 PM

i have just been fiddling with it and it still does the same thing. even changing the location that mysql_real_escape_String is applied.

i cant work it out. it worked fine before on the previous version of php. which leads me to believe it may be something in the config file or something they just changed in the most recent.

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 19 July 2006 - 11:26 PM

I've run it with 4.3 and 5.1 - same results
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users