Jump to content

Archived

This topic is now archived and is closed to further replies.

skideh

database adding stoppage

Recommended Posts

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

Share this post


Link to post
Share on other sites
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."

Share this post


Link to post
Share on other sites
[code]
<?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();
?>
[/code]

this is function output_post
[code]
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;
};
[/code]

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

Share this post


Link to post
Share on other sites
This si the simple table I set up

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

And this is the code that works
[code]<?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>[/code]

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.