How can I get information, located in a remote database, which is hosted by a Service Provider Company?
You cannot have any direct access to it, because the Service Provider does not allow this access level.
However, if you have a dynamic web site hosted in there that uses this database, it can query the database tables for you. Is this a strange goal?
I mean, I have not any Server availability but a hosted one. I maintain some dynamic sites available on internet, and the Service Provider hosts these sites.
I can use this service for other purpose, like to provide news to a program running on windows or any other operating system, Or to maintain license information in order to manage a network program key.
Many other ideas can arise of course.
The solution I suggest: is to query the database via php so that it will return results in JSON format.
JSON
JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. It is based on a subset of the JavaScript Programming Language, Standard ECMA-262 3rd Edition - December 1999. JSON is a text format that is completely language independent but uses conventions that are familiar to programmers of the C-family of languages, including C, C++, C#, Java, JavaScript, Perl, Python, and many others. These properties make JSON an ideal data-interchange language.
JSON example
{"news":[
{"title":"ver 1.0", "text":"this version …"},
{"title":"ver 1.1", "text":"this version …"},
{"title":"ver 1.2", "text":"this version …"}
]}
MySQL
MySQL is the RDBMS where are stored the information used for this example.
CREATE TABLE news ( `news_id` int AUTO_INCREMENT PRIMARY KEY, `product_id` int, `published` datetime, `title` nvarchar(100), `text` TEXT );
Image to have n records, where each of this reflects a new edition availability of your program.
PHP
Image to have a Php file that queries database using information passed by user, this is product_id. The query extracts the last published news and return a JSON file with a string that contains that information.
query_news.php:
$array = array();
$pid = $_POST["pid"];
$hostname = "localhost";
$database = "helpdesk";
$username="root";
$password="root";
$cnn = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
$sql = "SELECT published, title, text FROM news WHERE product_id = $pid ORDER BY news_id DESC LIMIT 1;";
$q = $cnn->query($sql);
$record = $q->fetch(PDO::FETCH_BOTH); //there is only one record returned!
$output = $record[0]."|".$record[1]."|".$record[2];
array_push($array, $output);
echo json_encode($array);
$cnn = null;
Many other improvements could be taken of course.
C# Here we are!
What we have to do now, is to call Php file in order to get some information about last news published.
Console example:
static void Main(string[] args)
{
string URI = "http://mydomain//query_news.php";
string myParameters = "pid=1";
using (WebClient wc = new WebClient())
{
wc.Headers[HttpRequestHeader.ContentType] = "application/x-www-form-urlencoded";
string sHtmlResult = wc.UploadString(URI, myParameters);
sHtmlResult = sHtmlResult.Substring(4);
sHtmlResult = sHtmlResult.Substring(0, sHtmlResult.Length - 2);
Console.WriteLine("" + sHtmlResult);
}
Console.ReadLine();
}
This simple console program uses a webclient service to call the Php file and shows the results.
Security
Once we have understand how the previous code works, it is possible to go ahead. For example using security precautions like integrated browser specifications TLS, SSL or simply a cryptographic functions implemented directly in the code. In this example I use The Advanced Encryption Standard (AES), also known as Rijndael (wiki).
Php Side:
class CryptoClass
{
public $ky = "lkirwf897+22#bbtrm8814z5qq=498j5"; // 32 * 8 = 256 bit key
public $iv = "741952hheeyy66#cs!9hjv887mxx7@8y"; // 32 * 8 = 256 bit iv
public function __construct() {
}
public function encrypt($string_to_encrypt)
{
$rtn = $this->encryptRJ256($this->ky, $this->iv, $string_to_encrypt);
return($rtn);
}
public function decrypt($string_to_decrypt)
{
$rtn = $this->decryptRJ256($this->ky, $this->iv, $string_to_decrypt);
return($rtn);
}
function decryptRJ256($key, $iv, $string_to_decrypt)
{
$string_to_decrypt = base64_decode($string_to_decrypt);
$rtn = mcrypt_decrypt(MCRYPT_RIJNDAEL_256, $key, $string_to_decrypt, MCRYPT_MODE_CBC, $iv);
return($rtn);
}
function encryptRJ256($key,$iv,$string_to_encrypt)
{
$rtn = mcrypt_encrypt(MCRYPT_RIJNDAEL_256, $key, $string_to_encrypt, MCRYPT_MODE_CBC, $iv);
$rtn = base64_encode($rtn);
return($rtn);
}
}
C# Side:
public class CryptoClass
{
string sKy = "lkirwf897+22#bbtrm8814z5qq=498j5"; //32 chr shared ascii string (32 * 8 = 256 bit)
string sIV = "741952hheeyy66#cs!9hjv887mxx7@8y"; //32 chr shared ascii string (32 * 8 = 256 bit)
public CryptoClass()
{ }
public string Decrypt(string prm_text_to_decrypt)
{
return DecryptRJ256(sKy, sIV, prm_text_to_decrypt);
}
public string Encrypt(string prm_text_to_encrypt)
{
return EncryptRJ256(sKy, sIV, prm_text_to_encrypt);
}
private string DecryptRJ256(string prm_key, string prm_iv, string prm_text_to_decrypt)
{
try
{
var sEncryptedString = prm_text_to_decrypt;
var myRijndael = new RijndaelManaged()
{
Padding = PaddingMode.Zeros,
Mode = CipherMode.CBC,
KeySize = 256,
BlockSize = 256
};
UTF8Encoding encoding = new UTF8Encoding();
var key = encoding.GetBytes(prm_key); //Encoding.ASCII.GetBytes
var IV = encoding.GetBytes(prm_iv);//Encoding.ASCII.GetBytes
var decryptor = myRijndael.CreateDecryptor(key, IV);
var sEncrypted = Convert.FromBase64String(sEncryptedString);
var fromEncrypt = new byte[sEncrypted.Length];
var msDecrypt = new MemoryStream(sEncrypted);
var csDecrypt = new CryptoStream(msDecrypt, decryptor, CryptoStreamMode.Read);
csDecrypt.Read(fromEncrypt, 0, fromEncrypt.Length);
return (encoding.GetString(fromEncrypt));//Encoding.ASCII.GetBytes
}
catch (Exception ex)
{
return "error: " + ex.Message + Environment.NewLine + prm_text_to_decrypt;
}
}
private string EncryptRJ256(string prm_key, string prm_iv, string prm_text_to_encrypt)
{
var sToEncrypt = prm_text_to_encrypt;
var myRijndael = new RijndaelManaged()
{
Padding = PaddingMode.Zeros,
Mode = CipherMode.CBC,
KeySize = 256,
BlockSize = 256
};
var key = Encoding.ASCII.GetBytes(prm_key);
var IV = Encoding.ASCII.GetBytes(prm_iv);
var encryptor = myRijndael.CreateEncryptor(key, IV);
var msEncrypt = new MemoryStream();
var csEncrypt = new CryptoStream(msEncrypt, encryptor, CryptoStreamMode.Write);
var toEncrypt = Encoding.ASCII.GetBytes(sToEncrypt);
csEncrypt.Write(toEncrypt, 0, toEncrypt.Length);
csEncrypt.FlushFinalBlock();
var encrypted = msEncrypt.ToArray();
return (Convert.ToBase64String(encrypted));
}
}
query_news.php:
$cry = new CryptoClass();
$array = array();
$input = $_POST["pid"];
if (!isset($input)) {
echo " Post Error parameter! ";
return;
}
$pid = chop($cry->decrypt($input), "\0");
$hostname = "localhost";
$database = "helpdesk";
$username="root";
$password="root";
$cnn = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
if (!$cnn) {
die("Server connection $hostname failed!");
}
try {
$sql = "SELECT published, title, text FROM news WHERE product_id = $pid ORDER BY news_id DESC LIMIT 1";
$q = $cnn->query($sql);
if ($q == false)
{
echo " SQL query error ([input=$input] [pid=$pid])! ";
return;
}
$q->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$record = $q->fetch(PDO::FETCH_BOTH);
$output = $record[0]."|".$record[1]."|".$record[2];
$output = $cry->encrypt($output);
array_push($array, $output);
echo json_encode($array);
}
catch (PDOException $e) {
$cnn = null;
array_push($array, 'Error: ' . $e->getMessage());
echo json_encode($array);
}
C# Console:
static void Main(string[] args)
{
CryptoClass cc = new CryptoClass();
string sURI = "http://localhost:33137//helpdesk//helpdesk//query_news.php";
string sKey = cc.Encrypt("1");
string myParameters = "pid=" + sKey;
using (WebClient wc = new WebClient())
{
wc.Headers[HttpRequestHeader.ContentType] = "application/x-www-form-urlencoded";
NameValueCollection reqparm = new System.Collections.Specialized.NameValueCollection();
reqparm.Add("pid", sKey);
byte[] responsebytes = wc.UploadValues(sURI, "POST", reqparm);
string sHtmlResult = Encoding.UTF8.GetString(responsebytes);
sHtmlResult = sHtmlResult.Substring(4);
sHtmlResult = sHtmlResult.Substring(0, sHtmlResult.Length - 2);
string sRes = cc.Decrypt(sHtmlResult);
Console.WriteLine("" + sRes);
}
Console.ReadLine();
}
Now you have enough elements to try yourself.
