Help With Php-Mysql Script

[maicol07]

Hi,

I have some PHP pages (from my last topic: https://www.helionet.org/index/topic/28805-dropdown-with-mysql-tables-names-of-a-database-as-options-and-php-mysql-errorssuggestions/) that integrates MySQL that don't work very well... here are my problems:

If you go on http://apps.maicol07.tk/app/sld/voti/ and you register/login you can see what doesn't work...

1. When you click on the pencil or the trash button (after you have inserted a new record with the + button on the right-bottom of the screen) it gives this error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE id=1' at line 2

   SQL QUERY:

   SELECT * FROM $materia
   WHERE id=$id;
   

2. The dropdown in the view.php (or view-paginated.php) file it is blank (except the first option that I've added) while on XAMPP it works...
3. The table of view.php doesn't work on Tommy, while on XAMPP it works.

Source Code: http://s000.tinyupload.com/index.php?file_id=87380380194489995693

 

Thanks

[Krydos]

check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE id=1' at line 2

Our servers use MySQL not MariaDB so I'm not sure how you're getting that error.

[maicol07]

Oh, because I pasted it from XAMPP... But the error on Tommy is

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id='1'' at line 1

 

Also in the view.php file there is an error between the buttons and the table that I don't understand:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

 

Today I'll see all the SQL statements in this area and I'll find what is wrong...

[maicol07]

I think that the configuration on XAMPP is different from the one on Tommy. I tried to use PHP Info on the two server and see what isn't activated. Here is the list:

• apache2handler
• Apache Environment
• HTTP Headers Information
• bz2
• odbc
• posix
• pspell
• readline

Maybe are these functions lost that cause the problems?

Edited August 7, 2017 by maicol07

[Krydos]

I just registered an account and got the verification email. I clicked the link in the email and it gave me a 404 error. Make sure the link you email out is correct.

[maicol07]

Oh, I was missing a / in the config file. I deleted you from the database so you can retry to register...

[maicol07]

Any solution?

[maicol07]

?

[wolstech]

These are malformed:

mysqli_query($connection,"DELETE FROM $materia WHERE id=$id")

They need ` around the field names, and the values need single quotes. It should be something like this (assuming $materia is a valid table name):

mysqli_query($connection,"DELETE FROM $materia WHERE `id`='$id';")

Also, your code is full of security holes. While I suppose I could assume they weren't added because the code doesn't even work yet, it's good practice to add this sort of stuff as you go. These are things like escaping and sanitization data being passed into SQL queries (look up "SQL injection" for more details)

[maicol07]

These are malformed:

mysqli_query($connection,"DELETE FROM $materia WHERE id=$id")

They need ` around the field names, and the values need single quotes. It should be something like this (assuming $materia is a valid table name):

mysqli_query($connection,"DELETE FROM $materia WHERE `id`='$id';")

Also, your code is full of security holes. While I suppose I could assume they weren't added because the code doesn't even work yet, it's good practice to add this sort of stuff as you go. These are things like escaping and sanitization data being passed into SQL queries (look up "SQL injection" for more details)

Thanks wolstech. How can I improve the security? I don't know about improve security...

Thanks again

[wolstech]

Do some research on "SQL injection". Basically, the issue is that you allow your users to enter data that's then directly sent to MySQL without being checked for MySQL's reserved characters/commands. There's nothing stopping someone from putting SQL commands in one of those inputs. Once someone does that, PHP just happily inserts their code into yours, MySQL runs it, and all sorts of things can happen.

 

For example, lets assume a simple search. In the below, $query is whatever a user types in a search box:

SELECT * FROM data WHERE `text` LIKE ('%$query%');

In normal cases, this is fine. If "code" was searched, you'd get queries like this after the variable is filled in:

SELECT * FROM data WHERE `text` LIKE ('%code%');

The above returns every result where `text` contains "code". This is what's supposed to happen, and a site with this code would work as expected.

 

Now, let's be evil...I type this in the search box:

'); DROP TABLE users; --

This results in the query becoming the following:

SELECT * FROM data WHERE `text` LIKE ('%'); DROP TABLE users; --%');

That query gets sent to the server, and the server happily runs each of the queries listed, in order. The server will return everything in data where `text` is % (wildcard meaning "anything"), then drop the users table.

 

You then come back later and wonder where your users table went...

[Krydos]

Relevant xkcd: https://xkcd.com/327/