Convert bytes to string task

[Clivar]

Hi,

Im making a database converter (phpbb to ipb)
I noticed during analysing both databases that the posts of phpbb are converted to bytes

as you can c here

-- Table structure for phpbb_posts
-- ----------------------------
CREATE TABLE `phpbb_posts` (
  `post_id` mediumint(8) unsigned NOT NULL auto_increment,
  `topic_id` mediumint(8) unsigned NOT NULL default '0',
  `forum_id` mediumint(8) unsigned NOT NULL default '0',
  `poster_id` mediumint(8) unsigned NOT NULL default '0',
  `icon_id` mediumint(8) unsigned NOT NULL default '0',
  `poster_ip` varchar(40) collate utf8_bin NOT NULL default '',
  `post_time` int(11) unsigned NOT NULL default '0',
  `post_approved` tinyint(1) unsigned NOT NULL default '1',
  `post_reported` tinyint(1) unsigned NOT NULL default '0',
  `enable_bbcode` tinyint(1) unsigned NOT NULL default '1',
  `enable_smilies` tinyint(1) unsigned NOT NULL default '1',
  `enable_magic_url` tinyint(1) unsigned NOT NULL default '1',
  `enable_sig` tinyint(1) unsigned NOT NULL default '1',
  `post_username` varchar(255) collate utf8_bin NOT NULL default '',
  `post_subject` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL default '',
  `post_text` mediumtext collate utf8_bin NOT NULL,
  `post_checksum` varchar(32) collate utf8_bin NOT NULL default '',
  `post_attachment` tinyint(1) unsigned NOT NULL default '0',
  `bbcode_bitfield` varchar(255) collate utf8_bin NOT NULL default '',
  `bbcode_uid` varchar(8) collate utf8_bin NOT NULL default '',
  `post_postcount` tinyint(1) unsigned NOT NULL default '1',
  `post_edit_time` int(11) unsigned NOT NULL default '0',
  `post_edit_reason` varchar(255) collate utf8_bin NOT NULL default '',
  `post_edit_user` mediumint(8) unsigned NOT NULL default '0',
  `post_edit_count` smallint(4) unsigned NOT NULL default '0',
  `post_edit_locked` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`post_id`),
  KEY `forum_id` (`forum_id`),
  KEY `topic_id` (`topic_id`),
  KEY `poster_ip` (`poster_ip`),
  KEY `poster_id` (`poster_id`),
  KEY `post_approved` (`post_approved`),
  KEY `tid_post_time` (`topic_id`,`post_time`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `phpbb_posts` VALUES ('1', '1', '2', '2', '0', '127.0.0.1', '1247936276', '1', '0', '1', '1', '1', '1', '', 'Welcome to phpBB3', 
0x5468697320697320616E206578616D706C6520706F737420696E20796F75722070687042423320696E7374616C6C6174696F6E2E2045766572797468696E67207365656D7320746F20626520776F726B696E672E20596F75206D61792064656C657465207468697320706F737420696620796F75206C696B6520616E6420636F6E74696E756520746F2073657420757020796F757220626F6172642E20447572696E672074686520696E7374616C6C6174696F6E2070726F6365737320796F75722066697273742063617465676F727920616E6420796F757220666972737420666F72756D206172652061737369676E656420616E20617070726F70726961746520736574206F66207065726D697373696F6E7320666F722074686520707265646566696E6564207573657267726F7570732061646D696E6973747261746F72732C20626F74732C20676C6F62616C206D6F64657261746F72732C206775657374732C207265676973746572656420757365727320616E64207265676973746572656420434F5050412075736572732E20496620796F7520616C736F2063686F6F736520746F2064656C65746520796F75722066697273742063617465676F727920616E6420796F757220666972737420666F72756D2C20646F206E6F7420666F7267657420746F2061737369676E207065726D697373696F6E7320666F7220616C6C207468657365207573657267726F75707320666F7220616C6C206E65772063617465676F7269657320616E6420666F72756D7320796F75206372656174652E204974206973207265636F6D6D656E64656420746F2072656E616D6520796F75722066697273742063617465676F727920616E6420796F757220666972737420666F72756D20616E6420636F7079207065726D697373696F6E732066726F6D207468657365207768696C65206372656174696E67206E65772063617465676F7269657320616E6420666F72756D732E20486176652066756E21,
 '5dd683b17f641daf84c040bfefc58ce9', '0', '', '', '1', '0', '', '0', '0', '0');
INSERT INTO `phpbb_posts` VALUES ('2', '1', '2', '2', '0', '127.0.0.1', '1248013556', '1', '0', '1', '1', '1', '1', '', 'Re: Welcome to phpBB3', 0x496D2061207370616D7A6F72, '70f9aa741b6128418d7fc20b0617b94b', '0', '', '3ffof7xe', '1', '0', '', '0', '0', '0');

I want to convert those to regular text again
but I have no clue how to script my converter to lookup the bytes place (after 0x) converts the string (as the bytes arent in array) and where to stop converting the bytes (after the ,)
then replace the bytes text by the regular text

and this needs to be in a loop because all posts need to be converted to normal text

I hope someone can help me with this issue

Regards

[ForumAccount]

You can use regex to extract that string:

Public Sub SomeMethod()
        Dim sqlStmt As String = "INSERT INTO `phpbb_posts` VALUES ('1', '1', '2', '2', '0', '127.0.0.1', '1247936276', '1', '0', '1', '1', '1', '1', '', 'Welcome to phpBB3', " & _
                                "0x5468697320697320616E206578616D706C6520706F737420696E20796F75722070687042423320696E7374616C6C6174696F6E2E2045766572797468696E6720736565" & _
                                "6D7320746F20626520776F726B696E672E20596F75206D61792064656C657465207468697320706F737420696620796F75206C696B6520616E6420636F6E74696E756520746" & _
                                "F2073657420757020796F757220626F6172642E20447572696E672074686520696E7374616C6C6174696F6E2070726F6365737320796F75722066697273742063617465676F727920616E6" & _
                                "420796F757220666972737420666F72756D206172652061737369676E656420616E20617070726F70726961746520736574206F66207065726D697373696F6E7320666F72207468652070726" & _
                                "5646566696E6564207573657267726F7570732061646D696E6973747261746F72732C20626F74732C20676C6F62616C206D6F64657261746F72732C206775657374732C20726567697374657265" & _
                                "6420757365727320616E64207265676973746572656420434F5050412075736572732E20496620796F7520616C736F2063686F6F736520746F2064656C65746520796F75722066697273742" & _
                                "063617465676F727920616E6420796F757220666972737420666F72756D2C20646F206E6F7420666F7267657420746F2061737369676E207065726D697373696F6E7320666F7220616C6C2074" & _
                                "68657365207573657267726F75707320666F7220616C6C206E65772063617465676F7269657320616E6420666F72756D7320796F75206372656174652E204974206973207265636F6D6D656E6" & _
                                "4656420746F2072656E616D6520796F75722066697273742063617465676F727920616E6420796F757220666972737420666F72756D20616E6420636F7079207065726D697373696F6E7320667" & _
                                "26F6D207468657365207768696C65206372656174696E67206E65772063617465676F7269657320616E6420666F72756D732E20486176652066756E21," & _
                                "'5dd683b17f641daf84c040bfefc58ce9', '0', '', '', '1', '0', '', '0', '0', '0');" & _
                                "INSERT INTO `phpbb_posts` VALUES ('2', '1', '2', '2', '0', '127.0.0.1', '1248013556', '1', '0', '1', '1', '1', '1', '', 'Re: Welcome to phpBB3', 0x496D2061207370616D7A6F72, '70f9aa741b6128418d7fc20b0617b94b', '0', '', '3ffof7xe', '1', '0', '', '0', '0', '0');"
 
        MessageBox.Show(System.Text.RegularExpressions.Regex.Match(sqlStmt, "0x[A-Za-z\d]+").Value)
    End Sub
    'EDIT
    'if you want to loop through you can do something like this
    For Each m As System.Text.RegularExpressions.Match In System.Text.RegularExpressions.Regex.Matches(sqlStmt, "0x[A-Za-z\d]+")
        MessageBox.Show(m.Value)
    Next

[Clivar]

Dim sqlStmt As String = RichTextBox1.Text
        For Each m As System.Text.RegularExpressions.Match In System.Text.RegularExpressions.Regex.Matches(sqlStmt, "0x[A-Za-z\d]+")
            Dim txt As String = m.Value
            txt = Replace(txt, "0x", "")
            MessageBox.Show(txt)

I stripped the 0x of it
I think thats needed to get it converted back to a string

but I just cant figure out how to convert it

anyone who can help to convert the text underneath back to text?

0x5468697320697320616E206578616D706C6520706F737420696E20796F75722070687042423320696E7374616C6C6174696F6E2E2045766572797468696E67207365656D7320746F20626520776F726B696E672E20596F75206D61792064656C657465207468697320706F737420696620796F75206C696B6520616E6420636F6E74696E756520746F2073657420757020796F757220626F6172642E20447572696E672074686520696E7374616C6C6174696F6E2070726F6365737320796F75722066697273742063617465676F727920616E6420796F757220666972737420666F72756D206172652061737369676E656420616E20617070726F70726961746520736574206F66207065726D697373696F6E7320666F722074686520707265646566696E6564207573657267726F7570732061646D696E6973747261746F72732C20626F74732C20676C6F62616C206D6F64657261746F72732C206775657374732C207265676973746572656420757365727320616E64207265676973746572656420434F5050412075736572732E20496620796F7520616C736F2063686F6F736520746F2064656C65746520796F75722066697273742063617465676F727920616E6420796F757220666972737420666F72756D2C20646F206E6F7420666F7267657420746F2061737369676E207065726D697373696F6E7320666F7220616C6C207468657365207573657267726F75707320666F7220616C6C206E65772063617465676F7269657320616E6420666F72756D7320796F75206372656174652E204974206973207265636F6D6D656E64656420746F2072656E616D6520796F75722066697273742063617465676F727920616E6420796F757220666972737420666F72756D20616E6420636F7079207065726D697373696F6E732066726F6D207468657365207768696C65206372656174696E67206E65772063617465676F7269657320616E6420666F72756D732E20486176652066756E21

[ForumAccount]

Have you had a look at the Convert class?

[Clivar]

no i didnt
I looked it up but I cant find a code that converts bytes into text :s

[Clivar]

found it

Function HexToString(ByVal hex As String) As String
    Dim text As New System.Text.StringBuilder(hex.Length \ 2)
    For i As Integer = 0 To hex.Length - 2 Step 2
        text.Append(Chr(Convert.ToByte(hex.Substring(i, 2), 16)))
    Next
    Return text.ToString
End Function