HBTIP, PHP, MySQL and ofcourse HMG

HMG Samples and Enhancements

Moderator: Rathinagiri

User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

HBTIP, PHP, MySQL and ofcourse HMG

Post by Rathinagiri »

Here is a small sample to connect to a MySQL database through a small PHP script using HBTIP.

This is acutally a preliminary one. Don't try this in live data.

Further for your information and using this sample, I had created a sample database which anybody can access using the following credentials.

dbhost : localhost
dbusername: diamone5_sample
dbpassword: sample
dbdatabasename: diamone5_sample

Here, I had given two files namely sample.php and main.prg.

Sample.php has already been uploaded to http://www.diamondcrackers.com/sample. Therefore the url for hbtip connection is http://www.diamondcrackers.com/sample/sample.php

Therefore you need not worry about sample.php.

Just compile and run main.prg and manipulate the things. :)

Sample database contains only one table namely 'master' with number, char, logic, date type fields with 2 records.

This is sample.php

Code: Select all

<?php

$password	= request_var('p', '');
$query	= request_var('s', '');
$rows = 0;
$cols = 0;

//echo $query;

/* create a connection object which is not connected */
$link = mysqli_init();

/* set connection options */
mysqli_options($link, MYSQLI_INIT_COMMAND, "SET AUTOCOMMIT=0");
mysqli_options($link, MYSQLI_OPT_CONNECT_TIMEOUT, 5);

/* connect to server */
mysqli_real_connect($link, 'localhost', 'diamone5_sample', $password, 'diamone5_sample');

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

//printf ("Connection: %s\n.", mysqli_get_host_info($link));

if ($result = mysqli_query($link, $query)) {

   $rows = mysqli_num_rows($result);
   $cols = mysqli_num_fields($result);   
   echo "$rows\n";
   echo "$cols\n";
   $i = 1;
    /* Get field information for all fields */
    while ($finfo = mysqli_fetch_field($result)) {
	   echo $finfo->type,"\n";
	   $i++;
    }
	$i = 1;
	while ($row = mysqli_fetch_row($result)) {
	   for ($j = 0; $j < $cols; $j++){
	      echo $row[$j],"\n";
		  }
       $i++; 	
	}
    mysqli_free_result($result);
}





mysqli_close($link);



function request_var($var_name, $default, $multibyte = false, $cookie = false)
    {
        if (!$cookie && isset($_COOKIE[$var_name]))
        {
            if (!isset($_GET[$var_name]) && !isset($_POST[$var_name]))
            {
                return (is_array($default)) ? array() : $default;
            }
            $_REQUEST[$var_name] = isset($_POST[$var_name]) ? $_POST[$var_name] : $_GET[$var_name];
        }
    
        if (!isset($_REQUEST[$var_name]) || (is_array($_REQUEST[$var_name]) && !is_array($default)) || (is_array($default) && !is_array($_REQUEST[$var_name])))
        {
            return (is_array($default)) ? array() : $default;
        }
    
        $var = $_REQUEST[$var_name];
        if (!is_array($default))
        {
            $type = gettype($default);
        }
        else
        {
            list($key_type, $type) = each($default);
            $type = gettype($type);
            $key_type = gettype($key_type);
            if ($type == 'array')
            {
                reset($default);
                $default = current($default);
                list($sub_key_type, $sub_type) = each($default);
                $sub_type = gettype($sub_type);
                $sub_type = ($sub_type == 'array') ? 'NULL' : $sub_type;
                $sub_key_type = gettype($sub_key_type);
            }
        }
    
       if (is_array($var))
       {
           $_var = $var;
           $var = array();
   
           foreach ($_var as $k => $v)
           {
               set_var($k, $k, $key_type);
               if ($type == 'array' && is_array($v))
               {
                   foreach ($v as $_k => $_v)
                   {
                       if (is_array($_v))
                       {
                           $_v = null;
                       }
                       set_var($_k, $_k, $sub_key_type);
                       set_var($var[$k][$_k], $_v, $sub_type, $multibyte);
                   }
               }
               else
               {
                   if ($type == 'array' || is_array($v))
                   {
                       $v = null;
                   }
                   set_var($var[$k], $v, $type, $multibyte);
               }
           }
       }
       else
       {
           set_var($var, $var, $type, $multibyte);
       }
   
       return $var;
   }
   
   function set_var(&$result, $var, $type, $multibyte = false)
    {
        settype($var, $type);
        $result = $var;
    
        if ($type == 'string')
        {
            $result = trim(htmlspecialchars(str_replace(array("\r\n", "\r"), array("\n", "\n"), $result), ENT_COMPAT, 'UTF-8'));
    
            if (!empty($result))
            {
                // Make sure multibyte characters are wellformed
                if ($multibyte)
                {
                    if (!preg_match('/^./u', $result))
                    {
                        $result = '';
                    }
                }
                else
                {
                    // no multibyte, allow only ASCII (0-127)
                    $result = preg_replace('/[\x80-\xFF]/', '?', $result);
                }
            }
    
            $result = (STRIP) ? stripslashes($result) : $result;
        }
    }


?>
This is main.prg

Code: Select all

# include "minigui.ch"

function main
local url := "http://www.diamondcrackers.com/sample/sample.php"
local pass := "sample"
local tmpfile := "temp.dat"
local qstr := "select * from master"

define window sample at 0,0 width 200 height 200 main

define button connect
   row 10
   col 10
   caption "Connect"
   action sql(url,pass,qstr,tmpfile)
end button



end window
sample.center
sample.activate

return nil

function sql(url,pass,qstr,cFile)
LOCAL oHttp, cHtml, hQuery, aLink, oNode, oDoc, arr1
oUrl := tURL():New( url+"?p="+pass+"&s="+parse_http(qstr) )
oHttp:= TIpClientHttp():new( oUrl )
IF .NOT. oHttp:open()
   msgstop("Connection error:", oHttp:lastErrorMessage())
   return nil
ENDIF
IF oHttp:ReadToFile( cFile )
//   msginfo("File "+ cFile + " written.")
   arr1 := txt2arr(cFile)
   if len(arr1) > 0
      msginfo("The query returns "+alltrim(str(len(arr1)))+" rows and "+alltrim(str(len(arr1[1])))+" column(s)")
   endif
endif 
return arr1



function parse_http(string)
local string1 := alltrim(string)
local space_char := "%20"
return strtran(string1," ",space_char)

/*
 CREATE TABLE `diamone5_sample`.`master` (
`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 30 ) NOT NULL ,
`date` DATE NULL ,
`logic` TINYINT NOT NULL DEFAULT '0',
`number` FLOAT NOT NULL DEFAULT '0.0'
) ENGINE = MYISAM 

/*

/*
MYSQLI_TYPE_DECIMAL :0
MYSQLI_TYPE_NEWDECIMAL:246
MYSQLI_TYPE_BIT:16
MYSQLI_TYPE_TINY:1
MYSQLI_TYPE_SHORT:2
MYSQLI_TYPE_LONG:3
MYSQLI_TYPE_FLOAT:4
MYSQLI_TYPE_DOUBLE:5
MYSQLI_TYPE_NULL:6
MYSQLI_TYPE_TIMESTAMP:7
MYSQLI_TYPE_LONGLONG:8
MYSQLI_TYPE_INT24:9
MYSQLI_TYPE_DATE:10
MYSQLI_TYPE_TIME:11
MYSQLI_TYPE_DATETIME:12
MYSQLI_TYPE_YEAR:13
MYSQLI_TYPE_NEWDATE:14
MYSQLI_TYPE_ENUM:247
MYSQLI_TYPE_SET:248
MYSQLI_TYPE_TINY_BLOB:249
MYSQLI_TYPE_MEDIUM_BLOB:250
MYSQLI_TYPE_LONG_BLOB:251
MYSQLI_TYPE_BLOB:252
MYSQLI_TYPE_VAR_STRING:253
MYSQLI_TYPE_STRING:254
MYSQLI_TYPE_GEOMETRY:255

*/
function txt2arr(_filename)

local arr := {}
local rowarr := {}
local types := {}
local rows := 0
local cols := 0
local string1 := ''
local date1 := ctod("  -  -    ")
local logic1 := .f.
local num1 := 0
local num2 := 0.0
local type1 := 0
local lines := {}
local handle := fopen(_filename,0)
local size1 := 0
local size2 := 0
local sample := 0
local lineno := 0
local eof1 := .f.
local linestr := ""
local c := ""
local len1 := 0
local x := 0
local finished := .f.
local m := 0
local length1 := 0
local linecount := 0
local length2 := 0
if handle == -1
   return nil
endif
size1 := fseek(handle,0,2)
size2 := size1
if size1 > 65000
   sample := 65000
else
   sample := size1
endif
fseek(handle,0)
lineno := 1
aadd(lines,"")
c := space(sample)
eof1 := .f.
linestr := ""
len1 := 0
do while .not. eof1
   x := 0
   x := fread(handle,@c,sample)
   len1 := len1 + sample
   if x < 1
      eof1 := .t.
      lines[lineno] := linestr
   else
      finished := .f.
      do while .not. finished
         m := at(chr(10),c)
         if m > 0
            if m == 1
               linestr := ""
               lineno := lineno + 1
               aadd(lines,"")
               c := substr(c,m+1,len(c))
            else
               if len(alltrim(linestr)) > 0
                  linestr := linestr + substr(c,1,m-1)
               else
                  linestr := substr(c,1,m-1)
               endif
               c := substr(c,m+1,len(c))
               lines[lineno] := linestr
               linestr := ""
               lineno := lineno + 1
               aadd(lines,"")
            endif
         else
            linestr := c
            finished := .t.
         endif
      enddo
      c := space(sample)
   endif
enddo
fclose(handle)

// processing the lines array
totlines := len(lines)
rows := val(alltrim(lines[1]))
cols := val(alltrim(lines[2]))
asize(types,0)
curline := 3
for i := 1 to cols
   type1 := val(alltrim(lines[curline]))
   do case
      case type1 == 2 // short
         aadd(types,"N")
      case type1 == 3 // long
         aadd(types,"N")
      case type1 == 8 // big int
         aadd(types,"N")
      case type1 == 4 // float
         aadd(types,"F")
      case type1 == 5 // double
         aadd(types,"F")
      case type1 == 10 // date
         aadd(types,"D")
      case type1 == 1 // tiny
         aadd(types,"L")
      case type1 == 253 // string
         aadd(types,"C")
      case type1 == 254 // string
         aadd(types,"C")
      case type1 == 6 // null (calculated field)
         aadd(types,"N")
      otherwise
         msgstop("Error in data type mismatch!")
         return nil
   endcase
   curline := curline + 1
next i
curline := curline + 1
set date ansi
i := curline
do while i <= totlines
   asize(rowarr,0)
   for j := 1 to cols
      if i > totlines
         msginfo(str(j))
         return nil
      endif 
      do case
         case types[j] == "C"
            aadd(rowarr,lines[i])
         case types[j] == "N"
            aadd(rowarr,val(alltrim(lines[i])))
         case types[j] == "D"
            aadd(rowarr,ctod(lines[i]))
         case types[j] == "L"
            aadd(rowarr,iif(val(alltrim(lines[i])) == 1,.t.,.f.))
         case types[j] == "F"
            aadd(rowarr,val(alltrim(lines[i])))
      endcase
      i++
   next j
   aadd(arr,aclone(rowarr))
enddo
set date ital
return aclone(arr)
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
raumi75
Posts: 135
Joined: Wed Dec 17, 2008 2:31 am
Location: Lünen, Germany
Contact:

Re: HBTIP, PHP, MySQL and ofcourse HMG

Post by raumi75 »

Hello,

it works like a charm! My compliments.

By opening http://www.diamondcrackers.com/sample/s ... m%20master you execute the query.

But this is very easy to hack, because there is no encryption. Someone might send a query containing something like DROP DATABASE; or DROP TABLE master. :twisted:

Sincerely

Raumi75
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: HBTIP, PHP, MySQL and ofcourse HMG

Post by Rathinagiri »

Thanks for your compliment.

I had already said it is a simple sample. We have to work a lot.

But, in the above example, if they don't know the password, they can't alter the database.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
Roberto Lopez
HMG Founder
Posts: 4004
Joined: Wed Jul 30, 2008 6:43 pm

Re: HBTIP, PHP, MySQL and ofcourse HMG

Post by Roberto Lopez »

rathinagiri wrote:Here is a small sample to connect to a MySQL database through a small PHP script using HBTIP.
<...>
Well... appears to be that I've created a bit of enthusiasm on you about this topic.... :)

Congratulations!

Regards,

Roberto.
Regards/Saludos,

Roberto


(Veritas Filia Temporis)
User avatar
Roberto Lopez
HMG Founder
Posts: 4004
Joined: Wed Jul 30, 2008 6:43 pm

Re: HBTIP, PHP, MySQL and ofcourse HMG

Post by Roberto Lopez »

raumi75 wrote:Hello,
<...>
But this is very easy to hack, because there is no encryption. Someone might send a query containing something like DROP DATABASE; or DROP TABLE master. :twisted:
But it will be very easy to fix, you only should send the password as a parameter. That's all.

Regards,

Roberto.
Regards/Saludos,

Roberto


(Veritas Filia Temporis)
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: HBTIP, PHP, MySQL and ofcourse HMG

Post by Rathinagiri »

Yes. I am doing so. Password for the database should be provided from the person who initiates the query.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
Roberto Lopez
HMG Founder
Posts: 4004
Joined: Wed Jul 30, 2008 6:43 pm

Re: HBTIP, PHP, MySQL and ofcourse HMG

Post by Roberto Lopez »

rathinagiri wrote:Yes. I am doing so. Password for the database should be provided from the person who initiates the query.
Sorry, You are right.

So, it appears to be reasonably secure for me.

Regards,

Roberto.
Regards/Saludos,

Roberto


(Veritas Filia Temporis)
User avatar
raumi75
Posts: 135
Joined: Wed Dec 17, 2008 2:31 am
Location: Lünen, Germany
Contact:

Re: HBTIP, PHP, MySQL and ofcourse HMG

Post by raumi75 »

rathinagiri wrote:Yes. I am doing so. Password for the database should be provided from the person who initiates the query.
You are right. And you could easily add filters that prevent an unpreviliged user from executing certain commands.

After I wrote my last post, I thought about it, again. Your example is no more insecure than any dbf-based database application. When you have a regular harbour-program, an evil user could also delete the database, because he needs write-previleges to the dbf-file.

I was just thinking about a web-application, where all sorts of SQL-injection attacks need to be addressed.

Raumi75
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: HBTIP, PHP, MySQL and ofcourse HMG

Post by Rathinagiri »

I think you can help in this regard Raumi75. :)
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
raumi75
Posts: 135
Joined: Wed Dec 17, 2008 2:31 am
Location: Lünen, Germany
Contact:

Re: HBTIP, PHP, MySQL and ofcourse HMG

Post by raumi75 »

rathinagiri wrote:I think you can help in this regard Raumi75. :)
Well, I am not an expert at all. A quick and dirty way to improve security, would be to pass the sql-Statement through a function like this:

Code: Select all

<?php. 
function SqlSelect($sql) {
	
	// A hacker could send more than one Statement
	// SELECT * FROM test, DROP DATABASE;
	$sqlStatements = explode(';', $sql);
	$sql = trim($sqlStatements[0]);       // Only the first Statement. Rest ist deleted
	// You could check the other statements to find out, if someone ist trying to hack.
		
	// check if the Statement contains unwanted commands
	$sqlForbidden = "/^DROP|DELETE|INSERT|UPDATE.*/";
	if (preg_match($sqlForbidden, $sql)) {
		echo "-- Hack attack!\n";
		$sql = "";
	}
	
	$sqlRequired = "/^SELECT.*FROM.*/";
	if (!preg_match($sqlRequired, $sql)) {
		 echo "-- Not a valid Select Statement!\n";
		 $sql = "";
	}
	
return $sql; // returns a valid SQL-Statement or an empty string.
}
?>
If you replace your Line 4 with
$query = sqlSelect(request_var('s', ''));
It will prevent the script from doing data-manipulation, if all you want is to return data. Or you could set up mysql-users with different privileges.

Please don't laugh at the imperfect function. It was just a quick writeup. I am sure, someone better than me will do a better job. It's just to demonstrate. :?

Jan
Post Reply