PHP/MySQL Security

The importance of database security cannot be overestimated. The planning of a database’s security may seem trite, but one must think of all the possible scenarios in which a potential hacker could access a database’s contents. There are many ways that unauthorized users can access content of a database. And it behooves the developer/programmer to implement strategies to create robust database access. That is, anything entered in a form should not be able to stop the PHP from executing or cause an unintended SQL query to take place. This means first checking for valid user input. The following illustration indicates a scenario that could possibly happen if the code did not check for valid user input.

Some things to avoid when designing a database:
• Avoid using common names for tables especially with open-source template databases.
• Avoid allowing error messages to display database-specific information to the user. Instead, send the data to a secured file perhaps one level above the root level on the production host where only an authorized user could access.
• Verify that the input data has the expected data type.
• Consider using stored procedures and previously defined cursors to abstract data access so that users do not directly access tables or views.
• Never connect to the database as a superuser or as the database owner to simply access data. Instead, always use a customized user login with very limited privileges.

The following example illustrates a secure way to compose a query for paging. The settype function will force the contents of the $offset variable to integer even if it is a string. For instance, if the value is “5bar,” its new value will 5. And if the value of $offset is a Boolean “true,” then the new value will be “1” as a string. This can add protection in that a user could not accidentally (or intentionally) put in a statement that could compromise database with a faulty $offset value.


Honesty and Integrity

Have you ever met someone who told you they would do something, and when the time came, they didn’t do it? This is an example where integrity is not at work. Integrity is a noble practice. If someone tells you they will do something, they definitely will do it. And when this person repeats this practice on multiple occasions, they will earn a reputation of having integrity. People know they can count on this person. You would be honest in telling them whether you can do [a given task] or not. This is a good reputation to have. People know they can count on you to say what you mean. If not, you would be practicing hypocrisy.

For example, if a web developer makes an agreement with a client to provide website assistance for 30 days. The client would expect that to be the case. If, for example, the client would not need assistance for the first 29 days; but then on the 30th day, a problem arises. If the web developer made this agreement with the client, then their reputation is at stake if they do not honor that request on the 30th day.

It is good for the developer to put all likely scenarios in writing and have the client sign so that all anticipated problems will be accounted for.

Suppose a client hires you to redesign their website. Their customer base consists of men and women. You learn that the client wants a female touch in the design (i.e., pink borders or perhaps flashing logos). Knowing this design would likely alienate there male customer base from visiting the website, there are ways you could practice integrity. For instance, you could suggest that the client ask a segment of their customer base to critique the mockup design. If the client chooses not to do such a thing, you may have to be frank and tell them about this potential loss [of male customers].

Internship Journal 6

I have learned that while setting up a database, one should never use a key as usable data. For instance, a telephone company would not want to use a customer’s phone number nor the Social Security Administration use a members’s social security number as primary keys. If a customer changes their phone number or a member passes away, references to the previous account may be awkward. Instead, create a unique primary key that would store the phone number or social security number for the respective organization and if there is a change to this [would be] keyed data, one can still access the record with an alternate key.

With the patient portal database, there would be a great number of tables. And many people and departments would have access to the database contents. With respect to HIPPA (Health Insurance Portability and Accountability Act) only authorized parties would have access to the read the database. These would include but not be limited to the patient, provider (doctor), imaging technician, radiologist and lab technician. Only specific parties would have CRUD (create read update delete) privileges. For example, the patient would have CRUD privileges for initially entering their health history and background while only having read privileges for a provider’s diagnoses or viewing lab or imaging results. An imaging technician would only have create privileges to store an image (CT scan, MRI, Ultrasound, etc.) And the radiologist would only have read access for the image and only create access for writing the image description.

I continued work on the illustrations for the grant proposal. In addition, I worked with Drupal so there is an admin instance where a user instance, one of two where I would log in as administrator and the other as a user. Each instance would be in a separate browser so that Drupal would likely not recognize that multiple instances are not present. This way, the user log-in would appear respectively while the admin would have its respective appearance with edit tabs.

I worked on the patient portal entity relationship diagram indicating the data for patient portal and the corresponding relationships.

Internship Journal 5

This time period involved more experimentation with Drupal to get the “Pages” module working. I consulted and worked on the Edmond Baha’i Faith site map. This process involved clarifying in more specific terms the functionality of each page. First of all, this design is a proposal to the Local Spiritual Assembly of the Baha’is of Edmond. It includes having not only a public site but also a member site for Baha’is to use as a work area. While each member would have a login account, content on the member site would not be available on the public site. The member site would consist of a community archive page where members would upload historical content (text, photos, timeline, videos and mp3 files) about the Baha’i Faith in Edmond and the Greater Oklahoma City area. And an authorized site user would propose to nominate specific content to place on the public site. And the member profile page would allow each member to opt in having part of their personal information available for viewing by other members. There would be a “calendar/news” page where the secretary would post community news. And respectively, the manager of the monthly calendar would post the calendar events as well. Before implementation, The Spiritual Assembly would have to approve this proposed design and functionality.

I also worked on the illustrations for the grant proposal to implement the Electronic Medical Records system mentioned in earlier journal entries. In doing so, I located and edited clip art indicating a pharmacy and loaded this into Photoshop in order to change its color so that it could be part of a health information exchange illustration indicating communication [in the cloud] between the Patient Portal, providers, payers, Lab, hospital and pharmacist.

Communication 2012

Communication is the process of sending a message from one person to another. This can be done with the spoken word, written word, visual imagery (fine art, photography, print advertisement, film, television, on-line video, audio), symbolic expression or sign language. Communication is the key to allow someone to do a variety of things such as: understand how someone feels, teaching students, inquiring on a question or offering an opinion or advice.

The internet, one of the primary communication forms of the modern era, can pay homage to Samuel Morse, the inventor of the telegraph who, on May 23rd, 1844, transmitted the first official electronic message “What hath God Wrought” between Washington DC and Baltimore.

Communication is a critical aspect in the business world. With the ease of access to the internet, one can easily communicate with others using a variety of web-based technologies to do a variety of tasks including managing projects with Basecamp and WebEx web applications.

Basecamp project management tool allows project team members to communicate in a variety of ways including: assigning tasks, setting calendar deadline dates, sharing files, modifying documents and participating in chat sessions.

WebEx allows members to attend virtual meetings with a separation of several thousand miles between each. Suppose, for example, member A in New Dehli, India, assumes controls over member B’s computer accessing files and navigating as if they were physically located at member B’s computer. That is, member A would offer a demonstration or provide technical assistance for member B on their computer. This is an example of the role that technology has had in the advancement of communication.

Upgrading phpMyAdmin from an Older Version

In order to upgrade phpMyAdmin from an older version, copy the / from the previous installation into the new version. You may need to modify the configuration files from the old version since some options have changed or would have been removed. Case in point, the definition of $cfg[‘AttributeTypes’] would have changed so it must be removed from the file so that one can use the default file. For compatibility with PHP6, remove reference to the set_magic_quotes_runitme(0) function, a statement that may exist at the end of your configuration file.

It is important to not copy libraries/config.default.php over since the default configuration file is version specific. If you upgrade your MySQL server from a version previous to 4.1.2 to version 5.x or newer you should run the SQL script found in scripts/upgrade_tables_mysql_4_1_2+.sql if you use the phpMyAdmin configuration storage.

The upgrade_tables_mysql_4_1_2+.sql file is meant for use with MySQL 4.1.2 and above. If one runs MySQL 4.1.0 or 4.1.1, it is recommended to create the tables using create_table.sql before using this script. Don’t forget to set up the table names in the file. The file will alter the ‘pma_bookmark’ table by doing one of many things: changing the ‘db_name’ column. If the table ‘pma_designer_coords’ does not exist the upgrade_tables_mysql_4_1_2+.sql database will create the table. There will be two primary keys of ‘db_name,’ and ‘table_name’ in addition to four integer variables: two defined as INT while the others are defined as TINYINT. This table also uses the ‘MyISAM’ engine.


Internship Journal 4

This week involved a variety of tasks including experimenting with Drupal themes and modules and drawing illustrations for the grant proposal to the Centers for Medicare and Medicaid (CMS).
I searched the on-line Drupal community to learn of a local users-group chapter meet-up next week for which I will attend to reinforce my knowledge of the content management system. Since my responsibilities are to create websites using Drupal, I will accomplish many tasks including the creation of pages with menu tabs, registering users, changing the design and layout and importing a raw MySQL database. I downloaded the Marinelli theme and experimented with creating pages with menu tabs. I also downloaded the Ctools and Views modules to assist in creating a page layout. I encountered a problem while creating basic page content in that it looks like a blog entry. My goal was to modify the layout so that the page looks as a static page. I can remove the cookie crumb trail and the ability for users to post comments which partially solves the problem. But with respect to creating a page that simply looks like static content, more work needs to be done.
With respect for the grant proposal to CMS, I created illustrations to accompany the narrative. The task included beginning with a North Carolina counties map and modifying the image with Photoshop to highlight the 16 counties in the state’s northeast region. In doing so, I imported the image into Photoshop and removed the other counties by first using the dropper tool to selecting the background color so that I could paint over the non-relevant counties with this color. After painting over the non-relevant counties with the background color, the entire image will highlight the 16 county region of interest. See the image above.
Then, I created a patient portal image to illustrate the content relevant to the patient for which the patient portal would store. The portal will store: calendar of appointments, medical history, health information, medical records and payer information. I also created an illustration indicating the Health Information Exchange (HIE), while used as a switch, would exchange information between the patient portal and the providers and between the payer and providers.