Expose the five common database titles that PHP uses to present in a program-Business logic code that includes database pattern design, database visits, and application databases-and their solutions.
Expose the five common database titles that PHP uses to present in a program-Business logic code that includes database pattern design, database visits, and application databases-and their solutions.
If there is only one method for applying the database to be accurate ......
You can use many methods to create database design, database visits, and database-based PHP business logic code, but it generally ends with errors. This article illustrates the five common headers presented in the PHP code for database design and database access, and how to fix these headers.
Title 1: directly apply MySQL
A common title is that older PHP code directly uses the mysql _ function to visit the database. Listing 1 shows how to directly visit the database.
Listing 1. Access/get. php
<? Php
Function get_user_id ($ name)
{
$ Db = mysql_connect ('localhost', 'root', 'password ');
Mysql_select_db ('users ');
$ Res = mysql_query ('select id FROM users WHERE login = ''. $ name .''');
While ($ row = mysql_fetch_array ($ res) {$ id = $ row [0];}
Return $ id;
}
Var_dump (get_user_id ('Jack '));
?>
Pay attention to the application of the mysql_connect function to visit the database. Pay attention to the query, and apply the string connection to add the $ name parameter to the query.
This technique has two good exchange plans: pear db module and PHP Data Objects (PDO) class. Both choose supply abstraction from a specific database. Therefore, your code can be stored in IBM without too much adjustment? DB2? , MySQL, PostgreSQL, or any other database you want to connect.
Another value of using the pear db module and the PDO abstraction layer is that you can apply it in SQL statements? Identifier. In this way, SQL is easier to protect and your application is protected from SQL injection attacks.
The change code for the application pear db is as follows.
Listing 2. Access/get_good.php
<? Php
Require_once ('Db. php ');
Function get_user_id ($ name)
{
$ Dsn = 'MySQL: // root: password @ localhost/users ';
$ Db = & DB: Connect ($ dsn, array ());
If (PEAR: isError ($ db) {die ($ db-> getMessage ());}
$ Res = $ db-> query ('select id FROM users WHERE login =? ', Array ($ name ));
$ Id = null;
While ($ res-> fetchInto ($ row) {$ id = $ row [0];}
Return $ id;
}
Var_dump (get_user_id ('Jack '));
?>
Note that all the locations that directly use MySQL are eliminated, except for the database connection strings in $ dsn. In addition, we pass? Apply the $ name variable to the SQL operator. Then, the queried data is sent in through the array at the end of the query () method.
Title 2: no active incremental effects applied
Like most modern databases, MySQL can create active incremental unique identifiers based on each record. In addition, we will still see the code, that is, first run a SELECT statement to find the largest id, then add this id to 1, and find a new record. Listing 3 shows an example of bad mode.
Listing 3. Badid. SQL
Drop table if exists users;
Create table users (
Id MEDIUMINT,
Login TEXT,
Password TEXT
);
Insert into users VALUES (1, 'Jack', 'pass ');
Insert into users VALUES (2, 'job', 'pass ');
Insert into users VALUES (1, 'Jane ', 'pass ');
The id field is specified as an integer. Therefore, although it should be unique, we can add any value, as shown in the INSERT statements following the CREATE statement. Listing 4 shows the PHP code that adds users to this type of mode.
Listing 4. Add_user.php
<? Php
Require_once ('Db. php ');
Function add_user ($ name, $ pass)
{
$ Rows = array ();
$ Dsn = 'MySQL: // root: password @ localhost/bad_badid ';
$ Db = & DB: Connect ($ dsn, array ());
If (PEAR: isError ($ db) {die ($ db-> getMessage ());}
$ Res = $ db-> query ('select max (id) FROM users ');
$ Id = null;
While ($ res-> fetchInto ($ row) {$ id = $ row [0];}
$ Id = 1;
$ Something = $ db-> prepare ('Insert INTO users VALUES (?,?,?) ');
$ Db-> execute ($ TH, array ($ id, $ name, $ pass ));
Return $ id;
}
$ Id = add_user ('Jerry ', 'pass ');
Var_dump ($ id );
?>
The code in add_user.php first executes a query to find the maximum value of the id. Then, the file adds 1 to the id value to run an INSERT statement. This code fails in the race condition on a server with heavy loads. In addition, it is also inefficient.
So what is the change plan? Apply the active incremental feature in MySQL to create a unique ID for each insert. The updated mode is as follows.
Listing 5. Goodid. php
Drop table if exists users;
Create table users (
Id mediumint not null AUTO_INCREMENT,
Login text not null,
Password text not null,
Primary key (id)
);
Insert into users VALUES (null, 'Jack', 'pass ');
Insert into users VALUES (null, 'job', 'pass ');
Insert into users VALUES (null, 'Jane ', 'pass ');
We have added the not null flag to trick the field from being NULL. We also added the AUTO_INCREMENT tag to enable the field to be automatically incremental, and added the primary key tag to trick that field into being an id. These changes speed up. Listing 6 shows the updated PHP code, which inserts users into the table.
Listing 6. Add_user_good.php
<? Php
Require_once ('Db. php ');
Function add_user ($ name, $ pass)
{
$ Dsn = 'MySQL: // root: password @ localhost/good_genid ';
$ Db = & DB: Connect ($ dsn, array ());
If (PEAR: isError ($ db) {die ($ db-> getMessage ());}
$ Something = $ db-> prepare ('Insert INTO users VALUES (null ,?,?) ');
$ Db-> execute ($ TH, array ($ name, $ pass ));
$ Res = $ db-> query ('select last_insert_id ()');
$ Id = null;
While ($ res-> fetchInto ($ row) {$ id = $ row [0];}
Return $ id;
}
$ Id = add_user ('Jerry ', 'pass ');
Var_dump ($ id );
?>
Now, instead of getting the largest id value, I directly apply the INSERT statement to INSERT data, and then apply the SELECT statement to retrieve the id of the last inserted record. This code is much simpler and more efficient than the original version and its coherent mode.
Title 3: apply multiple databases
By accident, we can see that in an application, each table is in a separate database. It is fair to do this in a very large database. However, for general exploitation programs, this level of separation is not required. In addition, Relational queries cannot be performed across databases, which affects the overall idea of applying relational databases, not to mention the difficulty of cross-database governance tables. So what should multiple databases look like? First, you need some data. Listing 7 shows the data that is divided into four files.
Listing 7. database files
Files. SQL:
Create table files (
Id MEDIUMINT,
User_id MEDIUMINT,
Name TEXT,
Path TEXT
);
Load_files. SQL:
Insert into files VALUES (1, 1, 'test1.jpg ', 'Files/test1.jpg ');
Insert into files VALUES (2, 1, 'test2.jpg ', 'Files/test2.jpg ');
Users. SQL:
Drop table if exists users;
Create table users (
Id MEDIUMINT,
Login TEXT,
Password TEXT
);
Load_users. SQL:
Insert into users VALUES (1, 'Jack', 'pass ');
Insert into users VALUES (2, 'Jon ', 'pass ');
In the multi-database version of these files, you should load SQL statements into one database, and then load the users SQL statements into another database. The PHP code used to query files associated with a specific user in the database is as follows.
Listing 8. Getfiles. php
<? Php
Require_once ('Db. php ');
Function get_user ($ name)
{
$ Dsn = 'MySQL: // root: password @ localhost/bad_multi1 ';
$ Db = & DB: Connect ($ dsn, array ());
If (PEAR: isError ($ db) {die ($ db-> getMessage ());}
$ Res = $ db-> query ('select id FROM users WHERE login =? ', Array ($ name ));
$ Uid = null;
While ($ res-> fetchInto ($ row) {$ uid = $ row [0];}
Return $ uid;
}
Function get_files ($ name)
{
$ Uid = get_user ($ name );
$ Rows = array ();
$ Dsn = 'MySQL: // root: password @ localhost/bad_multi2 ';
$ Db = & DB: Connect ($ dsn, array ());
If (PEAR: isError ($ db) {die ($ db-> getMessage ());}
$ Res = $ db-> query ('select * FROM files WHERE user_id =? ', Array ($ uid ));
While ($ res-> fetchInto ($ row) {$ rows [] = $ row ;}
Return $ rows;
}
$ Files = get_files ('Jack ');
Var_dump ($ files );
?>
The get_user function connects to the database containing the user table and retrieves the ID of the given user. The get_files function connects to the file table and retrieves the file rows associated with the specified user.
A better way to do all of these is to load the data into a database and perform the query, such as the following query.
Listing 9. Getfiles_good.php
<? Php
Require_once ('Db. php ');
Function get_files ($ name)
{
$ Rows = array ();
$ Dsn = 'MySQL: // root: password @ localhost/good_multi ';
$ Db = & DB: Connect ($ dsn, array ());
If (PEAR: isError ($ db) {die ($ db-> getMessage ());}
$ Res = $ db-> query ('select files. * FROM users, files WHERE
Users. login =? AND users. id = files. user_id ',
Array ($ name ));
While ($ res-> fetchInto ($ row) {$ rows [] = $ row ;}
Return $ rows;
}
$ Files = get_files ('Jack ');
Var_dump ($ files );
?>
This code is not only shorter, but also easier to understand and more efficient. We do not perform two queries, but perform one query.
Although the title sounds a little far-fetched, in practice, we usually sum up that all the tables should be in the same database, unless there is a very mandatory reason.
Title 4: No application relationship
Different from programming languages, relational databases do not have the array type. On the contrary, they apply the relationship between tables to create one to multiple structures between objects, which has the same consequences as arrays. One of the titles I see in the exploitation program is that engineers try to apply the database as a programming language by applying a text string with comma-separated identifiers to create an array. See the following mode.
Listing 10. Bad. SQL
Drop table if exists files;
Create table files (
Id MEDIUMINT,
Name TEXT,
Path TEXT
);
Drop table if exists users;
Create table users (
Id MEDIUMINT,
Login TEXT,
Password TEXT,
Files TEXT
);
Insert into files VALUES (1, 'test1.jpg ', 'media/test1.jpg ');
Insert into files VALUES (2, 'test1.jpg ', 'media/test1.jpg ');
Insert into users VALUES (1, 'Jack', 'pass', '1, 2 ');
A user in the system can have multiple files. In programming languages, arrays should be applied to represent objects associated with a user. In this example, the programmer selects to create a file field, which contains a list of file IDs separated by commas. To get a list of all files of a specific user, the programmer must first read rows from the user table, parse the file text, and run a separate SELECT statement for each file. The code is as follows.
Listing 11. Get. php
<? Php
Require_once ('Db. php ');
Function get_files ($ name)
{
$ Dsn = 'MySQL: // root: password @ localhost/bad_norel ';
$ Db = & DB: Connect ($ dsn, array ());
If (PEAR: isError ($ db) {die ($ db-> getMessage ());}
$ Res = $ db-> query ('select files FROM users WHERE login =? ', Array ($ name ));
$ Files = null;
While ($ res-> fetchInto ($ row) {$ files = $ row [0];}
$ Rows = array ();
Foreach (split (',', $ files) as $ file)
{
$ Res = $ db-> query ('select * FROM files WHERE id =? ',
Array ($ file ));
While ($ res-> fetchInto ($ row) {$ rows [] = $ row ;}
}
Return $ rows;
}
$ Files = get_files ('Jack ');
Var_dump ($ files );
?>
This technique is slow, difficult to protect, and does not make good use of the database. The only solution is to rebuild the model to switch it back to the traditional relational situation, as shown below.
Listing 12. Good. SQL
Drop table if exists files;
Create table files (
Id MEDIUMINT,
User_id MEDIUMINT,
Name TEXT,
Path TEXT
);
Drop table if exists users;
Create table users (
Id MEDIUMINT,
Login TEXT,
Password TEXT
);
Insert into users VALUES (1, 'Jack', 'pass ');
Insert into files VALUES (1, 1, 'test1.jpg ', 'media/test1.jpg ');
Insert into files VALUES (2, 1, 'test1.jpg ', 'media/test1.jpg ');
Here, each file is related to users in the file table through the user_id function. This may be the opposite of thinking of anyone who views multiple files as arrays. Of course, arrays do not reference the objects they contain-in fact, and vice versa. However, in relational databases, this is how it works, and queries are much faster and simpler. Listing 13 shows the corresponding PHP code.
Listing 13. Get_good.php
<? Php
Require_once ('Db. php ');
Function get_files ($ name)
{
$ Dsn = 'MySQL: // root: password @ localhost/good_rel ';
$ Db = & DB: Connect ($ dsn, array ());
If (PEAR: isError ($ db) {die ($ db-> getMessage ());}
$ Rows = array ();
$ Res = $ db-> query ('select files. * FROM users, files WHERE users. login =?
AND users. id = files. user_id ', array ($ name ));
While ($ res-> fetchInto ($ row) {$ rows [] = $ row ;}
Return $ rows;
}
$ Files = get_files ('Jack ');
Var_dump ($ files );
?>
Here, we perform a query on the database to obtain all rows. The code is not complex, and it uses the database as its original use.
Title 5: n 1 mode
I do not know how many times I have seen such a large exploitation program. The Code first retrieves some entities (such as customers) and then retrieves them one by one, obtain the specific information of each object. We call it the n 1 mode, because the query must perform so many times-one query to retrieve the list of all entities, and then perform a query for each of the n entities. When n = 10, this is not the title, but when n = 100 or n = 1000? Then it is determined that the title will be inefficient. Listing 14 shows an example of this pattern.
Listing 14. Schema. SQL
Drop table if exists authors;
Create table authors (
Id mediumint not null AUTO_INCREMENT,
Name text not null,
Primary key (id)
);
Drop table if exists books;
Create table books (
Id mediumint not null AUTO_INCREMENT,
Author_id mediumint not null,
Name text not null,
Primary key (id)
);
Insert into authors VALUES (null, 'Jack Herrington ');
Insert into authors VALUES (null, 'Dave Thomas ');
Insert into books VALUES (null, 1, 'Code Generation in action ');
Insert into books VALUES (null, 1, 'podcasting hacks ');
Insert into books VALUES (null, 1, 'php hacks ');
Insert into books VALUES (null, 2, 'pragmatic Programmer ');
Insert into books VALUES (null, 2, 'Ruby on Rails ');
Insert into books VALUES (null, 2, 'programming Ruby ');
This mode is reliable with no errors. The title is to visit the database to find the code for all the books of a given author, as shown below.
Listing 15. Get. php
<? Php
Require_once ('Db. php ');
$ Dsn = 'MySQL: // root: password @ localhost/good_books ';
$ Db = & DB: Connect ($ dsn, array ());
If (PEAR: isError ($ db) {die ($ db-> getMessage ());}
Function get_author_id ($ name)
{
Global $ db;
$ Res = $ db-> query ('select id FROM authors WHERE name =? ', Array ($ name ));
$ Id = null;
While ($ res-> fetchInto ($ row) {$ id = $ row [0];}
Return $ id;
}
Function get_books ($ id)
{
Global $ db;
$ Res = $ db-> query ('select id FROM books WHERE author_id =? ', Array ($ id ));
$ Ids = array ();
While ($ res-> fetchInto ($ row) {$ ids [] = $ row [0];}
Return $ ids;
}
Function get_book ($ id)
{
Global $ db;
$ Res = $ db-> query ('select * FROM books WHERE id =? ', Array ($ id ));
While ($ res-> fetchInto ($ row) {return $ row ;}
Return null;
}
$ Author_id = get_author_id ('Jack Herrington ');
$ Books = get_books ($ author_id );
Foreach ($ books as $ book_id ){
$ Book = get_book ($ book_id );
Var_dump ($ book );
}
?>
If you look at the following code, you may think, "Hey, this is really clear ." First, obtain the author id, the list of books, and information about each book. Indeed, it is clear, but is it efficient? The answer is No. See how many queries are performed to retrieve Jack Herrington's books. Get the id at a time, get the list of books at another time, and then perform a query for each book. Five queries are required for three books!
The solution is to use a function to perform a large number of queries, as shown below.
Listing 16. Get_good.php
<? Php
Require_once ('Db. php ');
$ Dsn = 'MySQL: // root: password @ localhost/good_books ';
$ Db = & DB: Connect ($ dsn, array ());
If (PEAR: isError ($ db) {die ($ db-> getMessage ());}
Function get_books ($ name)
{
Global $ db;
$ Res = $ db-> query ('select books. * FROM authors, books WHERE books. author_id = authors. id AND authors. name =? ',
Array ($ name ));
$ Rows = array ();
While ($ res-> fetchInto ($ row) {$ rows [] = $ row ;}
Return $ rows;
}
$ Books = get_books ('Jack Herrington ');
Var_dump ($ books );
?>
Now you need a quick and single query to retrieve the list. This means that I will probably need several methods with different parameters for these types, but there is no choice. If you want to have an extended PHP Exploitation program, you must apply the database effectively, which means more intelligent queries.
The title of this example is a bit clear. Generally, n 1 or n * n titles of these types are much more amazing. In addition, they are only displayed when the database administrator runs the query analyzer on the system when the system has the performance title.
Conclusion
Databases are powerful tools, just like all powerful tools. if you don't know how to apply them accurately, they will be abused. The trick to identify and solve these titles is to better understand the underlying skills. For a long time, I have heard business logic writers complain that they do not want to understand the database or SQL code. They treat the database as an object application and wonder why the performance is so poor.
They are not familiar with it and understand how important SQL is to convert databases from a tough necessity to a strong alliance. If you are familiar with SQL, you can browse The Art of SQL, which is very well written and practical.
