MYSQL

1
MYSQL PHP
2
My First Connection

• Whats Wrong?
• web app connects as root
• no checks for connectivity to the database
• raw data from the internet allowed in to your
  database

3
Failures

• Ways your page can break
• Someone can enter bad text
• Hacker delete from users
• User hows it going?
• Failure to connect to a database
• A query can return nothing when we expect somthing

4
Preventing SQL Injection

• Require remote users who can POST to authenticate
• Escape SQL input
• Yourself
• Let PHP do it

5
Preventing SQL Injection

• Only let un-authenticated users make sane
  requests
• Ex requesting an article
• http//myblog.com/disp-article.php?id900
• Even if you create all the links to your
  articles, others can still make fraudulent/wrong
  requests against your server

6
Preventing SQL Injection

• Blog example continued
• Throw out any requests
• Where the id is less than your lowest numbered
  entry
• Where the id is greater than your lowest numbered
  entry
• Where the client didnt request an integer value

7
In Class Work

• Create a php page that takes an input ID
• Ex mypage.php?idblah
• Test if _GETid is numeric
• PHP Function is_numeric()
• Test if _GETid is greater than or equal to
  the lowest ID value in your table
• Test if _GETid is less than or equal to the
  highest ID value in your table
• Use mysql_real_escape_string() to ensure there is
  no sql being inserted into your query

8
Bad HTML / JS

• Not harmful to your server
• Harmful to your end users
• An easy solution
• Strip_tags(input)
• Removes all HTML (and therefore all javascript)

9
Bad HTML / JS

• What happens in the real world
• Blogger
• Doesnt seem to do anything to prevent harmful
  HTML / Javascript
• Wordpress
• Makes it hard for users to enter raw HTML and
  provides plug-ins that generate HTML/JS when
  needed (to include a Youtube video, for example)
• Flickr
• Allows some HTML
• Must have some tricky regex running in the back
  to remove bad stuff

10
Graceful Failures
Good
Better yet (write your own error function that
deals with errors)
11
Graceful Failures
12
Using INCLUDE

• Your own connect function
• Add include db.php on every page
• Call db_connect() on the pages that require db
  connectivity
• Your own error_out function
• Allows
• Debug vs. production mode
• Custom outputs for different content types

13
Users / Privileges

• Database user ROOT has access to everything in
  the database
• Each web app should have its own user
• Each user should have the minimum set of
  privileges to accomplish the

Users dont have to be people a user can be an
application on your server
14
Add a User / Set its Privledges

• Go to your PHPMYADMIN page
• On the home page, click privileges
• Click add new user
• Add a user named websys
• that can
• Select
• Insert
• Update
• Delete

15
(No Transcript)