Database update from web page

Question

Post reply

Database update from web page

dtopicdragovic

Old Hand

Points: 335
More actions
July 18, 2012 at 9:11 am

#264319

Hi,

We have a SQL Server 2008 database that got updates from our Access front-end application.

Currently we are looking into developing a web page using PHP that we would send to our contacts and ask them to update their contact information. First thought was that we would connect to current db and do updates directly (validating fields before update). We have been asked to instead of updating directly into current db to update into some kind of temporary db or tables which would get some trigger that would advise admin that update happened. Then data would get checked by human and if everything is ok it would somehow get to the real db.

I was thinking about different scenarios, but I always get stuck because of the fact that there may be some problems with synchronization between current tables and temporary tables.

Is there some way of doing this that would be most appropriate?

Please let me know if there is missing info.

Thank you in advance

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

You could insert the data directly to a staging table on production. This does carry an injection risk but if you are validating your data application-side, this risk is mitigated.

Also create a stored procedure to load the inserted record from the stage table to production after the admin has checked it, then delete from the stage table.

I've done something similar to this before, PM me for more details.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

dtopicdragovic Old Hand Points: 335 More actions · Answer 2

dtopicdragovic

Old Hand

Points: 335

July 18, 2012 at 10:43 am

#1514356

Thanks a lot!

This definetly helps. I will let you know if I have any other questions!

DD

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

First thought was that we would connect to current db and do updates directly (validating fields before update).

Good Grief! First and foremost - NEVER do that. As a DBA, If I found out something like that was going on I would have nothing left to have nightmares about.

I concur with derek.colley that a staging table (that resides on a SQL instance residing in a DMZ or somewhere on the other side of your firewall(s)) along with a stored proc that cleans the staging table, archives the data and the data and inserts into your target DB is the way to go.

I was thinking about different scenarios, but I always get stuck because of the fact that there may be some problems with synchronization between current tables and temporary tables.

By temporary I suspect you mean staging; temp tables (#table, or ##tmp) have nothing to do with what you are discussing. The solution you are looking for will not work with temp tables.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

dtopicdragovic Old Hand Points: 335 More actions · Answer 4

dtopicdragovic

Old Hand

Points: 335

July 18, 2012 at 11:51 am

#1514402

🙂 I really appreciate these comments.

I learn a lot from them.

Yes, by temp I mean staging tables.

Thanks again for help.

DD

dtopicdragovic Old Hand Points: 335 More actions · Answer 5

One thing I forgot to mention is that when user login to web site we select his data and we display it to the user. After he updates it we want him to be able to see it.

Would be appropriate to do following:

1. on login we SELECT current info for this contact FROM production db

2. INSERT selected info to Staging tables

3. Show data from staging tables on the web site

4. User decides to update something. He changes info and click on "Update"

5. Data get refreshed on the web site from staging table.

6. I assume we need some kind of notification that he will receive confirmation when data is updated in our system.

7. In the meantime admin got alert that data has been updated in staging table and he checks

8. If ok then he runs SP to update real db.

9. Send notification to contact that info updated in our system

10. Delete records from staging table

We would probably need some check at the beginning if logged user already have info in staging table in case that contact returns before admin did his check.

Please let me know if I am missing something.

Thanks

DD

Sean Lange SSC Guru Points: 286562 More actions · Answer 6

XMLSQLNinja (7/18/2012)

First thought was that we would connect to current db and do updates directly (validating fields before update).

Good Grief! First and foremost - NEVER do that. As a DBA, If I found out something like that was going on I would have nothing left to have nightmares about.

I concur with derek.colley that a staging table (that resides on a SQL instance residing in a DMZ or somewhere on the other side of your firewall(s)) along with a stored proc that cleans the staging table, archives the data and the data and inserts into your target DB is the way to go.

I was thinking about different scenarios, but I always get stuck because of the fact that there may be some problems with synchronization between current tables and temporary tables.

By temporary I suspect you mean staging; temp tables (#table, or ##tmp) have nothing to do with what you are discussing. The solution you are looking for will not work with temp tables.

Speaking of nightmares...putting a sql instance in the DMZ??? That is seriously scary. The web client does not connect directly to SQL so why is the sql box outside??? It should be inside with a pinhole in the firewall that allows ONLY the IP of the webserver to make a connection.

The queries themselves need to be parameterized (or even better procs).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286562 More actions · Answer 7

dtopicdragovic (7/18/2012)

One thing I forgot to mention is that when user login to web site we select his data and we display it to the user. After he updates it we want him to be able to see it.

Would be appropriate to do following:

1. on login we SELECT current info for this contact FROM production db

2. INSERT selected info to Staging tables

3. Show data from staging tables on the web site

4. User decides to update something. He changes info and click on "Update"

5. Data get refreshed on the web site from staging table.

6. I assume we need some kind of notification that he will receive confirmation when data is updated in our system.

7. In the meantime admin got alert that data has been updated in staging table and he checks

8. If ok then he runs SP to update real db.

9. Send notification to contact that info updated in our system

10. Delete records from staging table

We would probably need some check at the beginning if logged user already have info in staging table in case that contact returns before admin did his check.

Please let me know if I am missing something.

Thanks

DD

So let me see if I get this. You are sending an email to your users to ask them to update their info. However, the form they fill out does not actually update their info. It will instead insert/update another table. This information needs to be approved by a human prior to "allowing" it. In the meantime however you want it to appear to the user that the data was in fact updated.

OK that sounds reasonable. You would not want to inform the user that their data was updated after a human validates it, as far as the user is concerned it already is (the info you want to display is the stuff they just entered right?). You would only want to tell the user something IF their new info is NOT approved. At this point you would "rollback" to their previous info.

One of the easiest ways to handle this is to create a second table for user info. Let's say the original table is named UserInfo. You could create a second table called UserInfo_NeedApproval or something like that. Then you change your display query.



select IsNull(na.FName, ui.FName) as FName,

ISNULL(na.LName, ui.LName) as LName,

etc...

from UserInfo ui

left join UserInfo_NeedApproval na on ui.UserID = na.UserID

where ui.UserID = SomeUserID

This makes the whole process simple from the query side. If there is newly entered data it will be retrieved, otherwise show the old data.

Hope that helps.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

dtopicdragovic Old Hand Points: 335 More actions · Answer 8

dtopicdragovic

Old Hand

Points: 335

July 18, 2012 at 2:33 pm

#1514548

Yes you are right , I want them to see what they have just enetered.

Thanks for such a detailed reply.

DD

dtopicdragovic Old Hand Points: 335 More actions · Answer 9

dtopicdragovic

Old Hand

Points: 335

July 19, 2012 at 8:19 am

#1514863

Do you think it is reliable to keep staging table (in your case UpdateInfo_NeedApproval table) in the same db as the production table (in your case UpdateInfo table)?

Our web site will have SSL and we plan on encrypting db with TDE.

Thanks

DD

Sean Lange SSC Guru Points: 286562 More actions · Answer 10

dtopicdragovic (7/19/2012)

Do you think it is reliable to keep staging table (in your case UpdateInfo_NeedApproval table) in the same db as the production table (in your case UpdateInfo table)?

Our web site will have SSL and we plan on encrypting db with TDE.

Thanks

DD

Sure it is just another table. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

dtopicdragovic Old Hand Points: 335 More actions · Answer 11

dtopicdragovic

Old Hand

Points: 335

July 19, 2012 at 8:25 am

#1514872

Thanks 🙂

dtopicdragovic Old Hand Points: 335 More actions · Answer 12

dtopicdragovic

Old Hand

Points: 335

July 19, 2012 at 9:53 am

#1514962

How would you deal with the case when contact wants to delete some info we have in our system?

Thanks

DD

Sean Lange SSC Guru Points: 286562 More actions · Answer 13

There are several ways you could handle this. One might be to change the ISNULL to check to a case statement.

Something like:



case when na.UserID is null then ui.FName else na.FName end as FName

That way you could have a NULL in the new values. Takes a bit more code but it would work. At least I think that is what you are asking?!?!?!?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

dtopicdragovic Old Hand Points: 335 More actions · Answer 14

I thought more in case when user deletes for example info in some specific field. Since I update just changed info into NeedApproval.Table, I wondered what I will do with deleted fields. How will I keep track of what has been deleted. I also have few other tables that are involved in this whole thing and user might want to delete a whole row in some cases.

I think that I should create stage tables for Delete operations too. ex. Delete_Contact that would function in similar way as NeedUpdate.Table, but just for deleted info.

Thanks

DD