B4A Class mysql_php class - easy way to connect webservice (security https & gzcompress)

Discussion in 'Additional libraries, classes and official updates' started by scsjc, Jul 9, 2017.

  1. scsjc

    scsjc Active Member Licensed User

    Hi all
    i share a easy code to get select from a database using a file on server (can use https)

    the first part of code is php to put on server with name: mysqlphp.php:
    Code:
    <?php
    if (isset($_GET['query'])) {$query = base64_decode(urldecode(htmlspecialchars($_GET['query'])));} else {exit;};
    if (isset($_GET['database'])) {$dbname = base64_decode(urldecode(htmlspecialchars($_GET['database'])));} else {exit;};

    //internacional code utf-
    8
    header (
    'Content-type: text/html; charset=utf-8');

    //datos conexion
    $localhost=
    "localhost";
    $user= USERNAME;
    $pass= PASSWORD;
    $connect_mysql=mysqli_connect($localhost,$user,$pass,$dbname);
    mysqli_set_charset($connect_mysql,
    "utf8");

    $return_arr = 
    array();
       $fetch = mysqli_query($connect_mysql,$query);

       
    while ($row = mysqli_fetch_array($fetch, MYSQLI_ASSOC)) {
            foreach ($row 
    as $field => $value)
            {
                $row_array[$field] = $value;
            
    }
            array_push($return_arr,$row_array);
        }
        $gzdata = base64_encode(gzcompress(json_encode($return_arr)));
        echo $gzdata;
        exit;
    ?>
    the second part of code is a class for b4a:
    Code:
    Sub Class_Globals
        
    Private CallBack As Object
        
    Private sqlquery As String
        
    Private database As String
    End Sub
    Public Sub Initialize(vCallback As Object, vSqlQuery As String, vDatabase As String)
        CallBack = vCallback
        sqlquery = vSqlQuery
        database = vDatabase
    End Sub
    Public Sub query
        
    Dim j As HttpJob
        j.Initialize(
    "", Me)
        j.Download2(
    "https://xxxxxxxx.com/mysqlphp.php"Array As String ("query",encode64(sqlquery),"database",encode64(database)))
        j.GetRequest.Timeout = 
    5000
        
    Wait For (j) JobDone(j As HttpJob)
        
    If j.Success Then
            
    Try
                
    Dim strResult As String = decode(j.GetString)
                
    If errInHtml(strResult) Then
                    j.Release
                    
    CallSub3(CallBack,"query_finish",FalseNull)
                
    End If
                
    Dim parser As JSONParser
                parser.Initialize(strResult)
                
    Dim rootlist As List = parser.NextArray
            
    Catch
                
    Log("mysqlphp_trycatch: " & LastException)
                j.Release
                
    CallSub3(CallBack,"query_finish",FalseNull)
            
    End Try
            
    CallSub3(CallBack,"query_finish",True, rootlist)
        
    Else
            
    CallSub3(CallBack,"query_finish",FalseNull)
        
    End If
        j.Release
    End Sub

    Sub errInHtml(txt As StringAs Boolean
       
    If txt="" Or txt.Contains("<TITLE>403 Forbidden</TITLE>")  Or txt.Contains("<TITLE>404 Not Found</TITLE>"Then
         
    Log("job errInHtml downloading 403 or 404")
         
    Return True
       
    Else
         
    Return False
       
    End If
    End Sub

    Sub decode(txt As StringAs String
       
    Dim strResult As String
       
    Dim cs As CompressedStreams
       
    Dim su As StringUtils
       
    Dim bt() As Byte
       
    Dim bc As ByteConverter
       
    Try
         bt = su.DecodeBase64(txt)
         bt = cs.DecompressBytes(bt, 
    "zlib")
         strResult = bc.StringFromBytes(bt, 
    "UTF8")
         
    Return strResult
       
    Catch
         
    Log(LastException)
         
    Return ""
       
    End Try
    End Sub

    Sub encode64(txt As StringAs String
       
    Dim su As StringUtils
       
    Return su.EncodeBase64(txt.GetBytes("UTF8")) 'data is a bytes array
    End Sub
    the thirt part of code is a call class b4a:
    Code:
    Dim mysqlphp1 As mysqlphp
        mysqlphp1.Initialize(Me,
    " select * from table ""db_name")
        mysqlphp1.query
        
    Wait For (mysqlphp1) query_finish(success As Boolean, lista As List)
            
    If success And lista.Size>0 Then
                
    For Each colroot As Map In lista
                    
    log(colroot.get("id"))
                    
    log(colroot.get("name"))
                
    Next
           
    end if
    End If
    You can call sentence to any row, or all rows. (The php only send the rows selected)
    select * from table
    select id, name from table
     
    Last edited: Jul 10, 2017
  2. DonManfred

    DonManfred Expert Licensed User

    this extension is deprecated as of PHP 5.5.0,
    Additionally you are suppressing any error.
     
    MarcoRome likes this.
  3. scsjc

    scsjc Active Member Licensed User

    Hi DonManfred
    is true...i change to: mysqli_connect
    the errors... i work in a new versions.... i want too put more sentences... (update....), now is really simple
     
    mangojack likes this.
  4. Mashiane

    Mashiane Well-Known Member Licensed User

    @scjsc wow, this is good stuff. I like. Whilst I can easily add CRUD functionality around your code to make it work for myself, perhaps you might consider doing that for other people in the forum who are learning things like this. Just a thought.

    On another note, we have learned not to trust user input at all costs, the query here is opening for SQL injections. Unless off-course the input query is controlled and checked for errors at entry level.

    I can just see this working perfectly for by b4a/b4i app. Thanks a million for this!
     
  5. DonManfred

    DonManfred Expert Licensed User

    Make sure you are not running an up to date PHP as the code above is using deprecated commands.

    If you need a good MySQLi-Class (Compatible with PHP 7): Here
     
  6. scsjc

    scsjc Active Member Licensed User

    Hello DonManfred, i see the MySQLi-Class and is good way to work with mysql. i'm try to use. thanks
     
  7. scsjc

    scsjc Active Member Licensed User

    Hello Mashiane, the code is a simple idea that I think.
    Being so simple is easy to understand and that each person adapts to their needs.
    I want to work on this idea better. I've been looking at MySQLi-Class (DonManfred said) and it's a very good choice.

    regards
     
  8. DonManfred

    DonManfred Expert Licensed User

    It is a really good class.
    If i create projects for customers and need a PHP-Bridge i always use this class.
     
  9. scsjc

    scsjc Active Member Licensed User

    Thanks for your recommendations.
     
  10. peacemaker

    peacemaker Well-Known Member Licensed User

    If to add some encription\decription on both sides - it would be even more useful.
     
  11. DonManfred

    DonManfred Expert Licensed User

    This is the wrong thread for your wish.
     
  12. peacemaker

    peacemaker Well-Known Member Licensed User

    It's not a wish, just suggestion to the author.
    BTW, where is the better thread ? I know that for any new question the new topic must be added.
    But this was not the question.
     
  13. DonManfred

    DonManfred Expert Licensed User

    The Author found out that his solution is outdated and he should use another one (like you too).
     
  14. rscheel

    rscheel Well-Known Member Licensed User

    For connection in mysqli php 7 and earlier versions.

    Code:
    <?php

    $servername = 
    "localhost";
    $username   = 
    "user";
    $password   = 
    "pass";
    $dbname     = 
    "dbname";

    /* crea la conexión */
    $conn = new mysqli($servername, $username, $password, $dbname);

    /* verificar la conexión */
    if ($conn->connect_error) {
        die(
    "Connection failed: " . $conn->connect_error);
    }

    /* cambiar el conjunto de caracteres a utf8 */
    if (!$conn->set_charset("utf8")) {
        printf("Error cargando el conjunto de caracteres utf8: %s\n", $mysqli->error);
        exit();
    }

    ?>
    The best method to manage MySQL connections with android in PHP PDO

    https://www.b4x.com/android/forum/threads/b4a-tutorial-sentencias-preparadas-php-pdo-mysql.69464/
     
    Last edited: Jul 12, 2017
  15. lazreg

    lazreg Member Licensed User

    @scsjc
    it's very simple ,, this is a good job ,, thank u so much .. i wish to find something like this in SqlServer

    @Mashiane
    if u did could u share?
     
Loading...