11 common mistakes made by PHP programmers

Source: Internet
Author: User
Tags mysql tutorial sql injection attack sql server express

1. Use myisam instead of innodb

The mysql tutorial has many database tutorial engines, but the most common examples are myisam and innodb.

Mysql uses myisam by default. However, in many cases this is a bad choice unless you are creating a very simple or experimental database. Foreign key constraints or transaction processing are very important for data integrity, but none of myisam supports these. In addition, when a record is inserted or updated, the entire data table is locked. When the usage increases, the operation efficiency is very poor.

Conclusion: innodb is used.


2. Use the mysql function in the php tutorial

The mysql library function (or near as makes no difference) is provided on the date of generation of php ). Many applications still use functions such as mysql_connect, mysql_query, and mysql_fetch_assoc, even though the php manual says:

If you are using mysql v4.1.3 or the latest version, we strongly recommend that you use the mysqli extension.

Mysqli (mysql enhanced version extension) has the following advantages:

Optional object-oriented Interfaces
Prepared expression, which can prevent SQL injection attacks and improve performance.
Supports more expressions and transaction processing
In addition, if you want to support multiple database systems, you can also consider pdo.


3. user input not processed

This can also be said as #1: Never trust user input. Use php on the server side to verify each string. Do not send Hope or webpage special effects. The simplest SQL injection attack uses the following code:


View sourceprint? 1 $ username = $ _ post ["name"];

2 $ password = $ _ post ["password"];

3 $ SQL = "select userid from usertable where username = '$ username' and password =' $ password ';";

4 // run query...


Enter "admin"; -- "in the username field. The corresponding SQL statement is as follows:

View sourceprint? 1 select userid from usertable where username = 'admin ';

Hackers can log on to the system as admin. They do not need to know the password because the password segment is commented out.


4. UTF-8 is not used.

In the United States, Britain, and Australia, we seldom consider languages other than English. We are proud to complete our "masterpiece", but we find that they cannot run normally elsewhere.

UTF-8 solves many international problems. Although it was not well supported before php v6.0, it does not affect you to set the mysql character set to UTF-8.


5. php preferred over SQL

If you are not familiar with mysql soon, you will prefer to use the language you have mastered to solve the problem, which will lead to writing redundant and inefficient code. For example, if you do not use the mysql avg () function, you will first sum the values in the record set and then use the php loop to calculate the average value.

In addition, pay attention to SQL queries in php loops. Generally, executing a query is more efficient than performing iteration in the result.

Therefore, when analyzing data, please take advantage of the database system, understanding some SQL knowledge will be of great benefit.


6. database query not optimized

99% of php performance problems are caused by databases. A poor SQL query can completely paralyze your web applications. Mysql's explain statement, query profiler, and many other tools will help you find these evil select statements.


7. Data Types cannot be correctly used

Mysql provides data types such as numeric, string, and date. If you want to store a time, use the date or datetime type. If the integer or string type is used at this time, the SQL query will be very complicated, provided that you can use integer or string to define that type.

Many people tend to customize some data formats without authorization, such as using string to store serialized php objects. In this way, database management may become simpler, but mysql may become a bad data storage and may cause faults.


8. Use * in Query *

Never use * to return data in all columns of a data table. This is laziness: You should extract the data you need. Even if you need all the fields, your data table will inevitably change.


9. No index or excessive use of Indexes

The general principle is as follows: indexes should be used for fields represented by any where clause in the select statement.

For example, suppose we have a user table, including numeric id (primary key) and email address. During login, mysql must find the correct id based on an email. If an index is used (email here), mysql can use a faster search algorithm to locate the email, or even implement it in real time. Otherwise, mysql can only check each record sequentially until the correct email address is found.

Some people will add indexes to each field. Unfortunately, after performing insert or update, these indexes need to be re-generated, which will affect the performance. Therefore, you only need to add an index as needed.


10. Forget the backup!

Although rare, the database is still at risk of crash. The hard disk may be damaged, the server may crash, and the web host provider may go bankrupt! The loss of mysql Data will be disastrous, so make sure that you have used automatic backup or copied it in place.


11. bonus mistake-do not consider using other databases

For php developers, mysql may be the most widely used database system, but it is not the only choice. Postgresql and firebird are the strongest competitors: both are open-source and not acquired by companies. Microsoft provides SQL server express and Oracle provides 10g express, both of which are free versions of enterprise-level databases. Sometimes, sqlite is a feasible alternative for a small web application or embedded application.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.