MOP - Mysql freedom is here, injection stop and bind parameter available

[azeez_ibrahim (Hazeezet)]

Mysql Optimizer

MOP is a php query handling and manipulation library providing easy and reliable way to manipulate query and get result in a fastest way.

Recomended Requirement

• PHP >= 7

Features

• Run query without limitation
• Bind parameter
• Mysql Injection stopped
• Stopping hackers from getting data from restricted Tables and databases
• Many more

Installing

You need a web server before you can use this library, Download the latest version in github and upload only the app inventor folder to your server.

most case you have to upload a zip file.

Rename the app inventor folder to any name you wish e.g mysql, then locate your path in the brower.

e.g https://yourwebsitename/rename/mysql.php

If you want to remove the php extension then rename mysql.php to index.php and your path will be

e.g https://yourwebsitename/rename/

If your browser show Bad Request Congratulations your Installation was successful.

FILES IN APP INVENTOR FOLDER

Downloading

• MOP Repository

Upgrading

When there a new version and you wish to upgrade, just reupload each file to the latest version that all and nothing will break, If there would be it will be specify in the change log.

Don’t forget if you rename any file.

Getting Started

You can check this out if you are beginner using mysql in App Inventor.

• Get started with mysql by Taifun .

NOTE : If you want to use this library then download it above.

To add Your database name and the rest edit mopconfig.php in the src folder

The red mark is where you put all table name or database name that you may think nobody should run a query contain those names.

The blue mark is your masterkey… is only use to run query that has the restriction above

That all about the configuration.

now to run a query from the app

BINDING OF PARAMETER

You will notice the question mark in the SELECT statement , yes you will put a question mark.

you can add as many parameter as you like the script is capable.

NOTE => the param you saw above is actually param[] it has open and closing square bracket…

Now no more injection… you pass user input separately and query also each query is verify which mean no any injection can occur no matter how you try.

if you are not clear enough feel free to comment your question

if you want to add more parameter then it will look like this

They must be arrange according to the question mark.

if you want to add your masterkey from admin app so you can run restricted column then it will look like this.

Now you have your freedom.

csv is return if there is any result.

Responds Code

• Responds code is 200 for any success query.
• Responds code is 206 if there is any error.

I know any error should be 400 but most hosting show custom error page rather than the error that occur that is the reason for using 206 and is the best for all hosting…

More

• start up block link
  • MOP_Startup_screen.ais (7.0 KB)

• Video ---- Youtube

[azeez_ibrahim (Hazeezet)]

Guide

• When inserting or updating a column with an empty string you will get an error because empty string is part of injection in some way.
  FOR EXAMPLE

INSERT INTO osql_table (ID,username,password) VALUES (’’,‘name’,‘password’);

you will notice the empty string before name… because of that you will get an error.
to avoid that you should use NULL instead or leave it blank.
this

INSERT INTO osql_table (ID,username,password) VALUES (NULL,‘name’,‘password’);

OR

INSERT INTO osql_table (username,password) VALUES (‘name’,‘password’);

the column is remove completely.

• When using a procedure to INSERT , UPDATE, SELECT and any other statement…only the result of the first SELECT statement is return .
  example.
  
  

Only the result of the first select statement is return which is at line 5…

To run a procedure is usually

CALL procudure_name();

NOTE:: You can also bind parameter in procedure… you put question mark where is expected and pass the parameter later. you should have gotten the gist in the introduction.

multiple select is available but it will be really complex in app inventor than a website, that is the best for now.

• When using a procedure to INSERT , UPDATE… without any SELECT statement the combination of affected row is return.

• When using a procedure with an out parameter, you will have to select the out parameter yourself.
  EXAMPLE
  

  

When i execute it

You will notice two statement

CALL osql_table(@p0);

NOTE :: you can bind anything bindable

SELECT @p0 as cc;

Now to achieve same result from the app.
your first statement will be your main query. then add the next query later.

what you saw above is actually add_query it does not support binding of parameter so it should be use mostly in procedure with out parameter.

if there is parameter you wish to pass then it should look like this.

• If you have a column that has number at the end eg column_1 and you are comparing it with a number eg where column_1 = 23.

SELECT * FROM table_name where column_1 = 23;

you will receive an error because number equal to number is part of mysql injection in some way.

to resolve that … change your column name to letters only… or remove any number at the end of your column name…

As i said you have your freedom to run anything and bind.

[emreTurkey (Emre)]

Hello there

Well done, but I couldn’t do the binding process, can you help me?

Thanks
good work

[azeez_ibrahim (Hazeezet)]

sure, show your query block as well as error message if there is any… i will see what i can do as well.

[emreTurkey (Emre)]

Hello
I did the same as what you wrote, but I didn’t learn much. Could you please explain the member registration, login, update processes and data extraction processes with a short example?

Thank you in advance

[azeez_ibrahim (Hazeezet)]

Okay wait let me make video about everything… and it will clear most people’s mind.

Best regards,
Hazeezet

[azeez_ibrahim (Hazeezet)]

Video link is on top…
also binding parameter bugs as been fixed and you can re test now…

[emreTurkey (Emre)]

thank you …

[azeez_ibrahim (Hazeezet)]

I try as much as possible to make it easy and avoid custom error page of hosting service…

The responds code of 204 that suppose to occur when there is any error i noticed it does not work so i have to change it to 206 ( partial content ) in the new update.

sorry for the frequent update and any error this may cause.

If there is any block that check for 204 responds code you have to change it 206 if you want to update.

---

---

Also there is a particular bug for now

If your query was a SELECT STATEMENT and there is no any result, maybe like finding or trying to select username that is not in your database…then instead of respond code to still be 200 responds code will change to 201.

[azeez_ibrahim (Hazeezet)]

Everything has been resolved…

now you have a block to check for number of rows

All responds now have a respond code of 200
If there is any error respond code is 206

so now you can be checking for number of rows before getting columns…

Now everything is good and okay without any bugs.

If your query was a insert statement you can check for number of rows as well

You can update your script… but note respond code of 201 is removed.

[emreTurkey (Emre)]

Hello again

I have two tables, I want to pull data with common id in both of them, but I couldn’t do it, can you help?

Respects

[azeez_ibrahim (Hazeezet)]

You will use inner join

SELECT first_table.name, second_table.name FROM first_table INNER JOIN second_table ON second_table.ID = first_table.ID  where first_table.name = anything;

Is actual an sql stuff… and dont forget to put your query in a proceure

[emreTurkey (Emre)]

I did but it didn’t work

[azeez_ibrahim (Hazeezet)]

what was the error and try to test your query in PHPmyAdmin before trying it in your app

[emreTurkey (Emre)]

It doesn’t give an error but it doesn’t show the data either

[emreTurkey (Emre)]

i am sharing the whole blog

[azeez_ibrahim (Hazeezet)]

You can not select using * when trying to get result from two table at once, specify your column name. and aslo try to run it in PHPMyAdmin if it works

[emreTurkey (Emre)]

I’m going to try it right away. Love and respect from Turkey to India

[azeez_ibrahim (Hazeezet)]

@emreTurkey

It doesn’t give an error but it doesn’t show the data ei

I did not see this block in the whole block you sent

[emreTurkey (Emre)]

No, I’m sending