Transcript PHP_Bible

PHP Bible
Chapter 25: PHP/Database Gotchas
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
1
 Wiley and the book authors, 2002
Summary







No connection
Problems with privileges
Unescaped quotes
Broken SQL statements
Too little data, too much data
Specific SQL functions
Debugging and sanity checking
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
2
 Wiley and the book authors, 2002
No connection

If you have a database call in your PHP script and the
connection can't be opened, you will see a version of one of
these 2 warning screens (depending on your error reporting
levels)

Warning: MySQL Connection Failed: Can't connect to MySQL
server on …


PHP "knows" about MySQL but the db server may be down, the
wrong server name was specified, or the server is listening on a
different socket
Fatal error: Call to undefined function mysql_conect()…

The MySQL module was not installed with PHP
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
3
 Wiley and the book authors, 2002
Problems with privileges

Error messages caused by privilege problems look a lot like the
connection errors described previously



Warning: MySQL Connection Failed: Access denied for user
'nohost@localhost' (Using password: YES)…
The key differentiator is that little piece about the user and
password
Caution: because of the security issues caused by these
warning messages, which include the username and host and
whether you're using a password or not, it's best to use silent
mode on a production site. You do this by putting the character
@ in front of the functions @mysql_connect and
@mysql_select_db
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
4
 Wiley and the book authors, 2002
Problems with privileges (cont.)


These errors are many in number, but fall into these major types:
 Mistyping usernames/passwords
 Failing to use a necessary password
 Trying to use a nonexistent username or password
 Trying to use your system's username/password instead of the MySQL
username/password
 Employing a database username that lacks the necessary permissions for
the task
 Logging in from a location or client that the MySQL database does not
allow for a particular user
 Being unable to open the database-password include file due to incorrect
file permissions (it must be world-readable in a world-executable
directory)
 The database root user having deliberately changed permissions on you
These are not structural problems, but usually just simple slips of memory
that result in miscues or mistaken memory and should be trivial to fix
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
5
 Wiley and the book authors, 2002
Unescaped quotes



Quotes can cause many small but annoying buglets between PHP and
MySQL
PHP evaluates within double quotes and largely ignores single quotes,
whereas MySQL evaluates within single quotes and largely ignores double
quotes
This can lead to situations where you have to think hard about the purpose of
each quotation mark
 mysql_query("INSERT INTO book (ID, title, year, ISBN)
VALUES(NULL, '$title', '$year', '$ISBN')");
 This is perfectly valid since the single quotes in the string are taken as
literal characters and the variables are interpolated to their values in the
double-quoted string
 This query would be broken if any of the variables have a single
(apostrophe) or double quote in them
If $title = 'Hello Kitty', $year = '1999', and $ISBN = 'AA1234', the string
passed to mysql_query would be "INSERT INTO book (ID, title, year,
ISBN) VALUES(NULL,'Hello Kitty', '1999', 'AA1234')"
 If $title = 'O'Hara', $year = '1999', and $ISBN = 'BB1234', the string passed
to mysql_query would be "INSERT INTO book (ID, title, year, ISBN)
VALUES(NULL,'O'Hara', '1999', 'BB1234')" which is clearly invalid
_______________________________________________________________________________________________________________

PHP Bible, 2nd Edition
6
 Wiley and the book authors, 2002
Unescaped quotes (cont.)


CAUTION: In very long text entries, a quote problem may
present as a partial string being inserted; or it may appear as a
complete failure; or it may seem as though only short entries are
being accepted while longer entries fail
In cases where the string is directly stated within your code, you
can escape the necessary characters with a backslash


In cases where the string is represented by a variable, you can
use addslashes() or mysql_escape_string()


$query = "INSERT INTO employee (lastname) VALUES ('O\'Donnel')";
$query = "INSERT INTO employee (lastname) VALUES
('".mysql_escape_string($_POST['lastname'])."')";
You can build PHP with the –with-magic-quotes option, and/or
set magic-quotes to on in the php.ini file, or use the
set_magic_quotes_runtime(1) function. This will add slashes
without your needing to specify addslashes() each time.
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
7
 Wiley and the book authors, 2002
Broken SQL statements


In addition to quoting problems, there are a number of easy
ways to send a "bad" query to the database.
That query might be syntactically malformed, have the right
syntax but refer to tables or fields that do not exist, or have any
of a number of problems that make the database unable to
handle it properly


E.g. printing the result of mysql_error(): You have an error
in your SQL syntax near 'UNIQUE FROM users' …
If you don't incorporate error checking into your query calls,
you will get the first warning from PHP when you attempt to
use the resource returned from the query

0 is not a mysql result identifier…
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
8
 Wiley and the book authors, 2002
Causes for SQL errors




Misspelled names: The single most common error is the
misspelling of table, field, or value names. It doesn't help that
PHP and MySQL are relatively case-sensitive (e.g. mytable !=
MyTable)
Comma faults: Remember to put the comma outside the single
quotes within an SQL statement. Don't put a comma after the
last field name in your SELECT statement
Unquoted string arguments: Any values that should be treated
by the database as string data types typically need to be singlequoted within an SQL statement
Unbound variables: One of the sneakier ways to break an SQL
statement is to interpolate an unbound variable into the middle
of it
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
9
 Wiley and the book authors, 2002
Too little data, too much data



You may find that your PHP/database script is working
apparently without error but is displaying no data from the
database, or far more than you expected
As a general rule: if your query function is returning
successfully, recheck the logic in your SQL statement
(especially in the WHERE clauses)
Another culprit of receiving more data than you were expecting
could be found in an SQL JOIN operation

As a general rule: the number of restrictions in a WHERE clause
should not be fewer than the number of tables joined minus one
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
10
 Wiley and the book authors, 2002
Specific SQL functions


A few specific functions seem to cause a higher than normal
number of problems, especially in the learning phase
mysql_affected_rows vs. mysql_num_rows


Both of these function tell you how many rows of data your last
SQL statement touched, but mysql_num_rows works only on
SELECT statements, while mysql_affected_rows works
only on INSERT, UPDATE, and DELETE statements
Additionally, since mysql_affected_rows shows the
number of affected rows from the last operation with the specified
(or unspecified) db connection, any subsequent calls to INSERT,
UPDATE, or DELETE anything on that db connection will
destroy the existing data returned from mysql_affected_rows and
replace it with the new data (even if the subsequent calls are made
from within a function call)
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
11
 Wiley and the book authors, 2002
Debugging and sanity checking




If you are nearing your wit's end in trying to debug query-related errors and
misbehavior, it can be extremely useful to actually compare the results of
your PHP-embedded queries with the same queries made directly to the
database (if your setup permits actually running an SQL client directly)
 Insert a debugging statement in your PHP script that prints the query
itself immediately before it's actually used in a db query call
 Directly paste that query from your browser output into your SQL client
If the query looks reasonable to you, but it breaks both in the SQL program
and in PHP, then there is some syntax or naming error in that SQL statement
itself that you are missing, and your PHP code is not to blame
If the behavior in the SQL interpreter looks like what you wanted, then the
query is fine and you should turn to your PHP code that actually sends that
query and processes the results
Finally, study any error messages very carefully, paying attention to phrases
like link identifier and result resource identifier
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
12
 Wiley and the book authors, 2002