MySQLi. PHP. GET. POST. Insert. Update. Delete. Segur SQL Inject

Hello friends,

I have published a basic tutorial on MySQLi and PHP in the App Inventor Community, I think it is better to put a link in this forum so as not to repeat the same thing.

The tutorial deals with 3 examples:
1.- MySQLi + PHP + GET
2.- MySQLi + PHP + POST
3.- MySQLi + PHP + Web page

In these examples I use the 000webhost server.

The forum link is:

I have also put it in Spanish:
App inventor. App inventor y MySQLi. PHP. GET. POST. Página web. Base de datos

Juan A. Villalpando,
Regards.

9 Likes

Congratulations ! Great tutorial!

@juananton1991
Can u add emojies data in text data type in mysql in this example???
I have tried almost all unicode queries many times but failed
Please chech if this is the error or problm in 000webhost server or something else???
Plaese guide me i have asked this in my post but no one proposed a proper and real solution

What are emonies?

Ohh sory i spelled wrongly
Actualy i m talking about emojies and other charcters

To upload emojies to MySQL:

http://kio4.com/appinventor/300_inicio.htm

I have tried this utf8mb4 queri but not worked for me in 000webhost
I will send u the aia in pm so that u can create your own table and then try

About SQL Injection.

In this interesting discussion it deals with security and SQL Injection in MySQL.

I’m going to follow this tutorial:


and I’m going to adapt my code to avoid SQL injection.

  • Note that the PHP code for managing MySQL is found in the server, hosting, not in the application.

mysqli_post_segur.php

<?php
// Juan Antonio Villalpando
// http://kio4.com/appinventor/340D_appinventor_mysqli_inject.htm

// 1.- IDENTIFICACION nombre de la base, del usuario, clave y servidor
$db_host="localhost";
$db_name="my_database";
$db_login="juan";
$db_pswd="contraseña";

// 2.- CONEXION A LA BASE DE DATOS
$link = new mysqli($db_host, $db_login, $db_pswd, $db_name);

if($link->connect_error) {
  exit('Error de conexion con la base de datos.');
}
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$link->set_charset("utf8mb4");

$boton = $_POST['boton'];

///////////////////////////////   INSERTAR - INSERT ////////////////////////////////////
if ($boton == "btnInsertar"){
    $Nombre = $_POST['Nombre'];
    $Edad = $_POST['Edad'];
    $Ciudad = $_POST['Ciudad'];
$stmt = $link->prepare("INSERT INTO personas (Nombre, Edad, Ciudad) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $Nombre, $Edad, $Ciudad);
$stmt->execute();
$stmt->close();
print("Datos agregados a la base.");
}

///////////////////////////////   BORRAR - DELETE  ////////////////////////////////////
if ($boton == "btnBorrar"){
    $Nombre = $_POST['Nombre'];
$stmt = $link->prepare("DELETE FROM personas WHERE Nombre = ?");
$stmt->bind_param("s", $Nombre);
$stmt->execute();
$stmt->close();
print("Datos borrados.");
}

//////////////////////////////   ACTUALIZAR - UPDATE  ///////////////////////////////
if ($boton == "btnActualizar"){
    $Nombre = $_POST['Nombre'];
    $Edad = $_POST['Edad'];
    $Ciudad = $_POST['Ciudad'];
$stmt = $link->prepare("UPDATE personas SET Edad = ?, Ciudad = ? WHERE Nombre = ?");
$stmt->bind_param("sss", $Edad, $Ciudad, $Nombre);
$stmt->execute();
$stmt->close();
print("Datos modificados.");
}

///////////////////// BUSCAR POR NOMBRE - SEARCH BY NAME /////////////////////////////
if ($boton == "btnBuscarNombre"){
	$Nombre=$_POST['Nombre'];
$stmt = $link->prepare("SELECT * FROM personas WHERE Nombre = ?");
$stmt->bind_param("s", $Nombre);
$stmt->execute();
$stmt->bind_result($id, $Nombre, $Edad, $Ciudad); 
     while ( $stmt-> fetch() ) {
          echo $id.",".$Nombre.",".$Edad.",".$Ciudad."\n";
     }
$stmt->close();
}

/////////////////////// MOSTRAR TABLA - SHOW TABLE  /////////////////////////////////////
if ($boton == "btnVerTabla"){
$stmt = $link->prepare("SELECT * FROM personas");
$stmt->bind_param();
$stmt->execute();
$stmt->bind_result($id, $Nombre, $Edad, $Ciudad); 
     while ( $stmt-> fetch() ) {
          echo $id.",".$Nombre.",".$Edad.",".$Ciudad."\n";
     }
$stmt->close();
}
/////////////////////// OBTENER ORDENADO - GET SORT  /////////////////////////////////////
if ($boton == "btnOrdenar"){
	$Columna = $_POST['Columna'];
$stmt = $link->prepare("SELECT * FROM personas ORDER BY $Columna ASC");
$stmt->bind_param();
$stmt->execute();
$stmt->bind_result($id, $Nombre, $Edad, $Ciudad); 
     while ( $stmt-> fetch() ) {
          echo $id.",".$Nombre.",".$Edad.",".$Ciudad.","."\n";
     }
$stmt->close();
}
///////////////////////////////////////////////////////////////////
?>
  • If you also use data encryption, for example with the com.KIO4_SecretKey.aix extension, your information will be more protected.

Regards,
(This Tutorial in Spanish)

1 Like

I’ve tried this and it works perfectly on a server with HTTPS

But then I wanted to try a free server (tonohost.com) and followed the same step and it didn’t work.

The only difference between the servers is that one has HTTPS and another one is HTTP.

Could it be that the problem is something related to the HTTPS security protocol?

What didn’t work? Any error messages?

I tried to simplify the code as much as possible to see if it was something else.

So I made a php file that just collects the POST data and displays it in a tag.

Here is the content of the PHP file:

<?php
include("dbconnect.php");


	$Fecha = $_POST['Fecha'];
	$Hora = $_POST['Hora'];
	


	print("\n Fecha= $Fecha \n Hora= $Hora");

	mysqli_close($conn);


?>

I let the connection to the database just to know if it was connected correctly.

Then in Kodular I use the web component to send the data by Post, as the tutorial says:

The server here is the 000webhost, which uses https

(The URL points to the file test.php, with the code above)

The result is this:

Now if I upload that same test.php file to the other server and put it in the URL, I get this:

The only thing that changes is the server. I uploaded the same test.php file.

Requesting enabled javascript … Did you ask support about any anti-bot system on the server?
Browser is up to date? ( Sorry answer posted wrong place )

I get the answer with Kodular’s web component…

I’ll check on the server if they have an option to enable Javascript, as you say

1 Like

I posted this answer in the wrong place … my fault

Many free hosting send a header in JavaScript that must be read by the Browser, if the Browser cannot read that header it causes an error.

You are working with tonohost.com with a free account.

0000webhost.com does not have that restriction.

this security system is mandatory on all free hosting accounts and cannot be disabled.

Thank you very much.
Then I will work with 000Webhost

@juananton1991 The link you put in says that bind_param supports 4 variable types (Integer, Double, String, Blob)
How do I save data such as name, age, city, and also save dates and times?

Example
bind_param(“sss”, $Edad, $Ciudad, $Nombre); mean Edad, Ciudad, Nombre are String,String,String: -----> sss

If you database is other types of fields…
i — inter
d — double
s — string
b — blob

In my example all fields are string.

ssi ----- > String,String,Integer

Yes, I understood that. My question was how do you set dates and times? But I read on other forums that it’s put as a string.
Thank you

@juananton1991 is your script able to solve following two problems of me