Any HUGE problems with this?

[crwtrue]

I am pretty new to php programming. I have made an program that reads lines from mysql database. Then it prints them to a html table and in that html table all the id fields are links. If you press a link, the program puts all the fields of that line in a number of html input fields. Then i can make changes to the data and when i press html submit button i open a new php page(before that i close the mysql connection) where i read the inputfields in to number of variables with $_POST['example']. After that i open a new connection and do an update query.

Everythink works but i dont know how secure is this way of doing this. Any advises? Any examples of proper way of doing this?:queasy:

[reddrum]

It seems to me that should work just fine as long as you sanitize any user input.
NogDog has been kind enough to provide an excellent MySQL class for PHP that includes the sanitize method I use http://www.charles-reace.com/PHP_and_MySQL/MySQL_Class/
This is the slightly modified version I use:

function sanitize($input){
   $this->connx = $this->open_conn();
   $input = trim($input);
   if(!is_numeric($input)){
      if(get_magic_quotes_gpc()){
         $input = stripslashes($input);
      }
      $input = sprintf("%s", ($this->connx) ? mysql_real_escape_string($input) : mysql_escape_string($input));
   }
      mysql_close();
      $this->connx = NULL;
      return $input;
}

[bpat1434]

I would suggest that you limit what items can be updated so that you don't allow for duplicate IDs, or messing up of translations or mappings from table to table.

Also, always remember: Fitler Input, Escape Output.

[crwtrue]

Thanks for the reply. The page that has the update query i made these changes to variables:

$id = intval($POST['id']);
$email = mysql_real_escape_string($POST['email']);
$fname=mysql_real_escape_string($_POST['fname']);
.
.
After this i do the query.

So is this a good way to make the update query safe?
Then i also put size attributes to the html input fields so user cannot put more text than is in the database fields. Other ideas what sould i do next?

Also my database is InnoDB(So it supports transactions) but i managed to do this:

i put those variable changes before mysql_connect and mysql_selectdb and i got number of mysql errors(cause i hadent log in to the database) but the query went through so i did have an empty line except the id.

So which kinda update line should i use to make sure this dosent happen again?

[bpat1434]

All the mysql_* functions need to be used after a connection is made. You won't hurt anything by moving the connection stuff above the data transformation.

Before you do any update query, you should always check the values to make sure everything you expect to be filled in is filled in. If not, then don't run the query, otherwise, run the query.

As for "which kinda update line should I use"? There is only one 😉 The difference is whether it's contained in a transaction or not. Transactions are good if you're doing more than one thing (like updating multiple tables, or manipulating data after doing an update) so that if something goes wrong, you can just "discard" your changes and know that nothing has changed. It's not a necessity; however, it is a nice thing to use.

[crwtrue]

bpat1434;10885891 wrote:

All the mysql_* functions need to be used after a connection is made. You won't hurt anything by moving the connection stuff above the data transformation.

Before you do any update query, you should always check the values to make sure everything you expect to be filled in is filled in. If not, then don't run the query, otherwise, run the query.

As for "which kinda update line should I use"? There is only one 😉 The difference is whether it's contained in a transaction or not. Transactions are good if you're doing more than one thing (like updating multiple tables, or manipulating data after doing an update) so that if something goes wrong, you can just "discard" your changes and know that nothing has changed. It's not a necessity; however, it is a nice thing to use.

Thanks for reply. Do you know any sites that would have some simple mysql + php tutorial on transactions?

I also added in to the update query page an delete query and if else statement for selecting which one to excecute. It probarly dosent matter that i do all the mysql steps in both. So if there comes $_POST['Delete'] i open the connection,do query, close connection. i thought it better that way that i dont leave any connections open :queasy:

Then i added the first page (which display the database table data in the html table) to an login site that i had done previously. Then i opened the site with two browers with diffrent user. The problem was that one person was looking the data in one row in the html input fiels when with other user i had allready deleted that line from the database. So how can i lock the table that this cannot be done?

Any examples how can you lock a table?

[bpat1434]

crwtrue wrote:

I also added in to the update query page an delete query and if else statement for selecting which one to excecute. It probarly dosent matter that i do all the mysql steps in both. So if there comes $_POST['Delete'] i open the connection,do query, close connection. i thought it better that way that i dont leave any connections open :S

Any open connections you have will be closed at the end of script execution. The only time you worry about that is if you use persistent connections. So feel free to start a connection up and leave it "open" until the end.

Then i added the first page (which display the database table data in the html table) to an login site that i had done previously. Then i opened the site with two browers with diffrent user. The problem was that one person was looking the data in one row in the html input fiels when with other user i had allready deleted that line from the database. So how can i lock the table that this cannot be done?

Any examples how can you lock a table?

From what I remember, InnoDB doesn't do table locking without you specifically telling it to lock the table. Now, this does not mean that another user won't be able to read from that table, it just means that another user won't be able to modify data in the table.

InnoDB, in order to ensure transaction stability, uses row-level locking by default. MyISAM uses table-locking because it has no transaction support. You can lock tables manually by using the LOCK TABLES[/man] syntax as defined in the MySQL manual.

Now, that's not exactly accurate. MySQL will actually queue the commands its given, and if you have say 5 users all in your admin area, they'll all see the same data (since a SELECT statement locks the table/row). Now, once they begin editing items, that's when blocking locking happens. If it's row-locking, then that specific row can't be touched (read from or modified) until the process that holds the lock is finished. Then the other processes will execute in an order of priority (with updates having a higher priority that SELECTs). So you will want to watch out for this.

The one problem I see with this is if you have conflicting updates. Say you have two users Bob and Randy. They log on roughly the same time (not exact, but a few seconds apart). Bob logged on first so you show him the most up-to-date data you have in the database. You show the same data to Randy who is a few seconds behind. Now Bob decides to modify something, so he goes to a form and edits it. Then Bob hits submit and you lock the table to Bob's thread. Now Randy, just after Bob hit submit, tries to edit the same data in that same table. Randy will now have to wait until after Bob's data has been updated before he can see the pre-filled out form to edit what he wants. But what if Randy went to the edit screen prior to Bob submitting his changes? Now Randy will see "old" data, and Bob will see a "successfully updated" message (presumably) and Randy has the potential to overwrite Bob's changes.

So then the question becomes, what is best from a usability standpoint? Is it better to lock the table to ensure the data is the same, or is it best to use row-locking so that the table is still usable by others while ensuring row-level data is consistent?

What happens if you forget to release that lock on the table in your code? Other users may wait up to 30 seconds (or more) as their processes wait to either get a lock on the table or wait to query the table. Extending the above example, say a third user, John, logs on. Bob has the lock, Randy is waiting on a lock, and John wants to query the table. John will see a blank screen until Randy's lock is released which won't happen until Bob's lock is released. So if the update from Bob takes a while, and Randy's query takes some time, then that lock could be there for a while and John might very-well time-out (if headers aren't sent to counteract this).

I think here is a good place for AJAX. One thing to do is to set up a timestamp column that updates (automatically) to the time that the row was updated. Then you query for any rows that were updated since X time (as defined by the AJAX request). Any rows updated since then need to be updated in the users browser. So your php will send back the data that is to be updated, and javascript will update the table as needed. Then the user will consistently see the most up-to-date info they can (given that the ajax checker will run in a continuous loop every 15 seconds or more).

Note you could also counteract "overwriting" of newer data by utilizing that timestamp field. I'll let you stew on that to see if you can figure it out 😉

[crwtrue]

The ajax solution sounds little tricky and i dont need anythink too fancy. Only that it works. Is there anyway you can make query take some predetermined time? So i could test if someone is doing a update query and then another user try to make a select query or somethink like that. I mean that lets say update query takes 1 sec but is it possible to make it last 30 sec?

[bpat1434]

I guess if you really wanted you could devise some timing mechanism, but why would you want to extend the time that other users can't do anything? That's negatively affecting the user experience. If anything, you'd want to minimize that time.

One thing you could do is utilize the locking system and use the MySQL function GET_LOCK('name', 0) to determine if the table is locked. If it returns "false" (0) then you know the table is locked, and show a message to the user asking them to wait, and try again in 5 or 10 seconds. If you get the lock, then proceed with whatever you were doing.

Not sure if that would help, but it's worth a shot.

[crwtrue]

bpat1434;10885942 wrote:

I guess if you really wanted you could devise some timing mechanism, but why would you want to extend the time that other users can't do anything? That's negatively affecting the user experience. If anything, you'd want to minimize that time.

One thing you could do is utilize the locking system and use the MySQL function GET_LOCK('name', 0) to determine if the table is locked. If it returns "false" (0) then you know the table is locked, and show a message to the user asking them to wait, and try again in 5 or 10 seconds. If you get the lock, then proceed with whatever you were doing.

Not sure if that would help, but it's worth a shot.

i didnt get that query Get_lock function to work i put the $id field to it as an parameter but no. I now have two new querys, before update lock tables ... and later unlock tables query.
It works fine. How about if someone is doing an transaction and one part of it is updating the table which i locked. Does the transaction wait for the lock to be opened? how long before timeout? Any examples how to use the timestamp field proberly?
I also deleted all the data in those php common variables like $_GET than when i go to the previous page it works same than when you first came to the page. Is this how things are normally done in php programming?😕

[bpat1434]

If you lock a table, then nothing can be done on that table by anyone else until that lock is released. So if user A locks a table, and then starts a transaction, then the transaction will proceed since user A holds the lock. If user B then starts a transaction on a table that user A has locked, the transaction user B is starting will wait patiently until they can obtain a lock (which will happen after user A releases the lock).

how long before timeout?

Not sure about this, but probably around 30 seconds.

Any examples how to use the timestamp field proberly?

I don't have any examples; however, I can explain it.
[indent]You have a form that allows editing of data. Inside that form there is a hidden field for the timestamp. Now, two users (Andy and Bob) are editing the same data. They both opened the same row of data and are seeing the exact same form. Andy finishes his edits quicker than Bob and submits the form first. Your script takes the timestamp from the form (remember it's a hidden field) and compares it to the timestamp of that particular row in the database. Since they're the same, then the row is continued to be updated. During the update, you also update the timestamp field to be the current timestamp (using the NOW() function in MySQL).

Now Bob finishes his edits, after Andy, and submits the form. Bob still has the "old" timestamp hidden in his form. Your script checks Bob's timestamp from the form with that from the database. Since Bob's timestamp is older than the one in the database, you know there is a conflict. So your script stops processing the form, gets the current database values and spits back out an error message and the form again. The error informs them that there have been edits since they started edits. The form now contains the most up-to-date information from the database.[/indent]

That's how that would work.

[crwtrue]

I did following addon to my program. I made class that hold 3 private functions for insert,delete and update. There is also one public funcion which have if -else sentence which choose what function to call. Is this a smart add on to the program? i didnt make constructor or destructor cause there aint any child classes.. :queasy:

[laserlight]

I made class that hold 3 private functions for insert,delete and update. There is also one public funcion which have if -else sentence which choose what function to call. Is this a smart add on to the program? i didnt make constructor or destructor cause there aint any child classes.

Why not discard that public function and instead declare those three functions for insert, delete and update as public? If you do want that other public function, then another possibility would be to declare it abstract, then have Insert, Delete and Update child classes override it to implement the desired behaviour.

[crwtrue]

well i did it like that cos i thought it would make it more secure, but yeah it really dont make any diffrence to use private cos there is only one class..
i really dont want to leave any chance that someone could alter lets say delete query. Also that one function that calls the query functions have code that ask if user is certain of his choice or not.