TMySQL - MySQL and MariaDB connection

[ricardo arraes]

Hey everybody!

I'm a little bit away from the forum these days... 2021 has come with a lot of work (thanks God!), but now I'm here to leave some contribution to our community!

TMySQL is a class created by me (based on WDO and ORM) which allows the application to properly connect to a MySQL/MariaDB database .

simple as that...

In order to use TMySQL at you application, you must build it inside mercury. it means that you only have to save it inside the mercury/lib directory, include it at the mercury.prg and build mercury.
*Charly is analyzing TMySQL, possibly, in the future, WDO will have all this features as well

After rebuilding mercury, TMySQL will be a native class and all you gotta do is create some environment variable inside you .htaccess file like this:

SetEnv SGBD "MARIA"
SetEnv HOST "localhost"
SetEnv USER "user"
SetEnv PASSWORD "psw"
SetEnv SCHEMA "schema"

*SGBD must be MARIA or MYSQL

These environment variables will allow you to call App():oBD which is your application's connection object. I'll explain it's methods later in this post.

*If you need to create another connection, all you gotta do is call a TMySQL:New(cSgbd, cHost, cUser, cPsw, cSchema, nPort) with your connection parameters

Well, we all know that security is a requirement when we talk about the web, so when I created this class I implemented some good practices that are useful and can help you improve the security of you application.

I'll explain theses good practices right now:

Never insert your parameters directly at your query

Inserting your parameters at your query like this:

"Select * from table where '"+param01+"'"

without "sanitizing" your parameters, is not considered a good practice, because it exposes your application to many threats.
TMySQL has a soution to that:

1. Requesting data

We can use the Query method, like that:

Código: Seleccionar todo

LOCAL oTable, aParam:={}

Aadd(aParam,"x")
Aadd(aParam,"y")
oTable:=App():oBD:Query("Select * from table where field1='PARAM01' AND field2='PARAM02'",aParam)

the Query method from our oBD object is responsible for escaping (sanitizing) our the parameters inside the aParam array before inserting them in our query as you can see:

Código: Seleccionar todo

METHOD Query(cSQL, aParam)
   
   LOCAL oTable,nCount

   if ! Empty( ::pLib )
      ::hMySQL = mysql_init(::pLib)        
      
      if ::hMySQL != 0
         ::hConnection = mysql_real_connect( ::pLib,::cHost, ::cUser,IF(::cPsw # NIL,::cPsw, AP_GETENV( 'PASSWORD' )), ::cSchema, ::nPort, ::hMySQL )
         
         if ::hConnection != ::hMySQL
            ? "Error on connection to server " + ::cHost,::cSchema,::cUser,::hConnection , ::hMySQL, ::cLibName, mysql_error(::pLib,::hMySQL)
            RETURN NIL
         endif
      endif 

      IF .NOT. Empty(aParam)
         FOR nCount:=1 TO Len(aParam) 
            mysql_real_escape_string_quote(::pLib,::hConnection,@aParam[nCount])
            cSQL:=StrTran(cSQL,"PARAM"+StrZero(nCount,2),IF(Type(aParam[nCount]) # "C",IF(Type(aParam[nCount]) = "D", Dtoc(aParam[nCount]),hb_ValToStr(aParam[nCount])),aParam[nCount]))
         NEXT nCount
      ENDIF

      ::nRetVal := mysql_real_query( ::pLib, ::hConnection, cSQL )     

      if ::nRetVal != 0
         oTable:=NIL
      else
         oTable = MySqlTable():New(Self)
      endif 

      mysql_close(::pLib,::hConnection)
   else
      ? ::cLibName + " not available"     
      RETURN NIL
   endif   
 RETURN oTable 

1. Manipulating data

Manipulating data is just as simple as requesting it, but the difference here is that we can process as many queries as we want with the same connection.

Código: Seleccionar todo

LOCAL aParam:={}, aQuery:={}

AAdd(aParam,{"x","y"})
AAdd(aParam,{"a","b","c"})     
Aadd(aQuery, "DELETE FROM table WHERE field1='PARAM01' AND field2='PARAM02'") 
Aadd(aQuery, "INSERT INTO table (field1, field2, field2) "+;
                   "VALUES ('PARAM01','PARAM02','PARAM03')") 

App():oBD:Exec(aQuery,aParam)

Código: Seleccionar todo

METHOD Exec( aSQL, aParam )

   LOCAL nArq, nCount, nCountCol

   if ! Empty( ::pLib )
      ::hMySQL = mysql_init(::pLib)        
         
      if ::hMySQL != 0
         ::hConnection = mysql_real_connect( ::pLib,::cHost, ::cUser, IF(::cPsw # NIL,::cPsw, AP_GETENV( 'PASSWORD' )), ::cSchema, ::nPort, ::hMySQL )
         
         if ::hConnection != ::hMySQL
            //? "Error on connection to server " + ::cHost,::hConnection , ::hMySQL
            RETURN NIL
         endif
      endif 

      FOR nCount:=1 TO Len(aSQL)
         IF aParam # NIL .AND. Len(aParam[nCount]) > 0        
            FOR nCountCol:=1 TO Len(aParam[nCount])
               mysql_real_escape_string_quote(::pLib,::hConnection,@aParam[nCount,nCountCol])
               aSQl[nCount]:=StrTran(aSQL[nCount],"PARAM"+StrZero(nCountCol,2),IF(Type(aParam[nCount,nCountCol]) # "C",IF(Type(aParam[nCount,nCountCol]) = "D", Dtoc(aParam[nCount,nCountCol]),hb_ValToStr(aParam[nCount,nCountCol])),aParam[nCount,nCountCol]))
            NEXT nCountCol
         ENDIF
         mysql_real_query( ::pLib,  ::hConnection, aSQL[nCount] )     
      NEXT nCount
      
      if mysql_error( ::hMySQL ) # NIL         
         nArq := FCreate("MySql.log")
         FWrite( nArq, mysql_error( ::hMySQL ) )
         FClose( nArq )
      endif 

      mysql_close(::pLib,::hConnection)
   else
      //? ::cLibName + " not available"     
      RETURN NIL
   endif   

 RETURN NIL

TMySQL also has a method that allow us to convert a JSON object into a MySQLTable object, making it easier to exchange data between web applications or webservices.

Código: Seleccionar todo

METHOD JsonToTable(hJson)
   
   LOCAL oTable

   oTable = MySqlTable():New(Self,hJson)

 RETURN oTable

Código: Seleccionar todo

METHOD New( oOrm, hJson ) CLASS MySQLTable
 
    local n, m, hField, hRow

    ::Super:New( oOrm)

    IF hJson = NIL
 
      ::hMyRes = mysql_store_result( oOrm:pLib, oOrm:hConnection )
   
      if ::hMyRes == 0
         ? "mysql_store_results() failed"
      else
         ::aFields = Array( mysql_num_fields( oOrm:pLib, ::hMyRes ) )
         
         for n = 1 to Len( ::aFields )
            hField = mysql_fetch_field( oOrm:pLib, ::hMyRes )
            if hField != 0
               ::aFields[ n ] = Array( 4 )
               ::aFields[ n ][ 1 ] = PtrToStr( hField, 0 )
               do case
                  case AScan( { 253, 254, 12 }, PtrToUI( hField, hb_SysMyTypePos() ) ) != 0
                        ::aFields[ n ][ 2 ] = "C"
   
                  case AScan( { 1, 3, 4, 5, 8, 9, 246 }, PtrToUI( hField, hb_SysMyTypePos() ) ) != 0
                        ::aFields[ n ][ 2 ] = "N"
   
                  case AScan( { 10 }, PtrToUI( hField, hb_SysMyTypePos() ) ) != 0
                        ::aFields[ n ][ 2 ] = "D"
   
                  case AScan( { 250, 252 }, PtrToUI( hField, hb_SysMyTypePos() ) ) != 0
                        ::aFields[ n ][ 2 ] = "M"
               endcase 
            endif   
         next   
   
         ::aRows = Array( mysql_num_rows( oOrm:pLib, ::hMyRes ), ::FCount() )
   
         for n = 1 to Len( ::aRows )
            if ( hRow := mysql_fetch_row( oOrm:pLib, ::hMyRes ) ) != 0
               for m = 1 to ::FCount()
                  ::aRows[ n, m ] = PtrToStr( hRow, m - 1 )
               next
            endif
         next         
   
      endif
   ELSE
      ::aFields:= hJson['header']
      
      ::aRows  := hJson['body']

   ENDIF
    
 return Self   

You can find TMySQL right here:

https://github.com/ricbarraes/TMySQL

[ramirezosvaldo]

Ricardo

Suppose this sample

cSQL := "UPDATE table FIELD='PARAM01' WHERE ID = PARAM02"

THEN

:query( cSQL , { 'nuevo dato',1} )

Then the real_query would look like this:

UDATE table FIELD='nuevo dato' WHERE id = 1

But, if the value 'nuevo dato' contain an double quote or single quote, How it look like ?

Thanks in advance
Osvaldo Ramirez

[ricardo arraes]

Saludos estimado Osvaldo!

First of all, let me make a simple consideration... instead of using Query(), you should use Exec() for INSERT, UPDATE and DELETE. Query() is just for SELECT (retrieving data)

Now let’s get to the point of your question...

With TMySQL you don’t really need to worry about apecial characters because inside the Exec() and Query() methods there’s a mysql_escape() function that will automatically “escape” the special characters of your parameter in order to avoid errors and risky situations.

For example:

I want to execute a query like:

Código: Seleccionar todo

App():oBD:Exec(UPDATE table SET field=‘PARAM01’ WHERE field2=‘PARAM02’,{“Mc Donald’s”,”Sh’k%”})

*sorry for the poor parameters example

Before TMySQL attach this parameters to your query it will “escape” them and, at the end, your query will look like this:

Código: Seleccionar todo

UPDATE table SET field=‘Mc Donald\’s’ WHERE field2=‘Sh\’k\%’

But, if you are having any trouble, give me an example so I can check it!

https://dev.mysql.com/doc/refman/8.0/en ... nsi_quotes

[pquiroz]

Ricardo I tried TMsql, it worked very well, but I have a question if it would be prudent to save keys the encrypted data in the .htaccess file

Best regards
Pablo

[ricardo arraes]

Hey Pablo!

thanks for your question!

Before I answer it, let's talk a little bit about the .htaccess file

By default, the .htaccess file is protected by your web server and is not reachable through the web. which means that theorically it's a "safe place" to keep this kind of information.

(to make sure your .htaccess file is indeed protected, you can follow the instructions on this website right here: https://htaccessbook.com/protect-htaccess-files/)

With that been said...

yes, it's QUITE prudent doing it...
but why did I just say "Quite"?

because, when we are talking about the web, there's no place and application 100% safe... all we can do is apply some good pratices at our development routine in order to increase the security of our application.

Probably there are other techniques and ways to keep this information even more protected, but, to be honest with you, these days I didn't have much time to spend thinking about (but I will spend, soon), so in order to keep things working and develop good applications with some level of security, I think this should be a good starting point!

If you have any suggestion or experience with it, please, make yourself comfortable to share it with us!

[ricardo arraes]

P.S:

if you did the default installation of your apache server (via xampp or not), in your httpd.conf file probably there's this block right here:

Código: Seleccionar todo

#
# The following lines prevent .htaccess and .htpasswd files from being 
# viewed by Web clients. 
#
<Files ".ht*">
    Require all denied
</Files>

this block is denying the access to every file which it's name starts with ".ht". So, as I said, .htaccess is protected by default...

and there's more!

if your .htaccess file contains this block right here:

Código: Seleccionar todo

<IfModule mod_rewrite.c>
	RewriteEngine on
	RewriteCond %{REQUEST_FILENAME} !-f
	RewriteCond %{REQUEST_FILENAME} !-d
	RewriteRule ^(.*)$ index.prg/$1 [L]
</IfModule>

the line

Código: Seleccionar todo

RewriteRule ^(.*)$ index.prg/$1 [L]

is redirecting every URL/request to the index.prg (if you are using mercury, it will stop at your Router and probably will display a 404 error)

[ramirezosvaldo]

Muchas Gracias Ricardo!!!

Saludos

[charly]

Ricardo,

Thanks for your tips

They are very interesting

Maybe we should try using the mysqli extensions ? (instead mysql)

C.

[ricardo arraes]

Hey Charly!

Thanks for your suggestion!

Would you mind explaining what are the advantages of mysqli?

If there are any advantages, yes, of course! we should do it!

[ricardo arraes]

Just made some quick research about it, Charly!
Extremely interesting!

https://en.wikipedia.org/wiki/MySQLi

I'm going to make some changes and test it!

Thank you again, Charly!