1. About SQL injection
So far, I have not seen who wrote a very complete article, or a very mature solution (can do a lot of people, the problem is not spread out, very sorry) I simply said a few, hope to inspire people to think, play a role in the discussion
First, the principle of SQL injection
The implementation of SQL injection and the destruction of a lot of, but same, the principle can be summed up as a word: SQL injection is to the server side to submit prepared data, pieced together the attacker wanted SQL statements to change the database operation execution Plan.
I think that may not be a refinement, but the meaning should be very clear, this sentence mainly contains so three layers of meaning:
1. What are the ways in which attackers are injected?
Where SQL injection vulnerabilities exist is where applications need to construct SQL statements based on the client environment. It can be inferred that, as long as there is a "client data substitution predefined variable" place, it is possible to be injected.
There are a number of ways that clients can submit data: Get,post,client-agent,cookie,server enviroment ...
2. Why can an attacker "inject" the statement it wants?
Because the server-side application uses a patchwork of SQL statements (please pay special attention to the word), it gives the attacker the opportunity to include SQL keywords or operators in the submitted data to construct the statements they want.
3.SQL injection What is the end result?
Change the database operation execution Plan.
This result is not necessarily malicious, as long as your SQL statement does not execute as you intended (plan), it can be seen as being injected, regardless of whether the person submitting the data is malicious.
There are such SQL statements:
Update tablename Set columnName1 = "$Client _submit_data" where pk_id = 1234
$Client _submit_data is a variable that represents the data submitted by the client, and I don't care whether the environment is ASP or PHP or something else.
Suppose the operation is to update the title of an article, does a lot of people construct SQL statements like this? Let's look at the $CL ient_submit_data contains quotes, so $client_submit_data = who can tell me what "SQL Injecti on" is?
Then the SQL statement will be pieced together like this:
Update tablename Set columnName1 = "Who can tell me what" SQL injection "is? "Where pk_id = 1234
The execution result is obvious, and the statement will be executed: UPDATE tablename Set columnName1 = "Who can tell me?"
The WHERE clause is ignored, and unfortunately, all the article titles in your database will be called "Who can tell me?"
In this example, the user should be unintentional--the title contains quotes should be normal--but the result is the same as the SQL injection.
Well, for a long time, let's talk about how to deal with this problem.
I believe that friends here have seen a lot of prevention of SQL injection articles, but also through the replace to prevent some injection, the question is: Do you know the reason for it?
I think the best way to completely solve SQL injection is to avoid piecing together SQL statements. That's why I want you to pay special attention to the word patchwork.
SQL injection because the vast majority of server application use a patchwork of SQL statements to build applications (for those of you who read this post, when you look back at your project, how many of them do not manipulate the database by piecing together SQL statements?) Think about the injected cases you've seen, there are several applications that are not used to put together SQL statements, the so-called patchwork of SQL statements, the simple point is: the use of connection string operations (& in ASP and PHP) to connect the SQL keyword and client-submitted data to the DBMS execution. This directly causes the DBMS to have no idea what you are planning to do, but only what you are going to do, isn't it, the server-side script always constructs the SQL statement that will be executed, and then sends it to the database, and the DBMS doesn't know that the client data has been replaced with the variable, Whether the execution plan for this statement has changed. Server-side scripting always tells the DBMS rudely: you just do it, don't ask me why. As I mentioned above, the example of updating the title of the article, the DBMS does not know that you just want to update the title of the 1234th article, it thought you just want to make all the headings into this, because your statement is no WHERE clause!
Speaking of this, perhaps everybody understands, the so-called best method is stored Procedure. yes! That is!
To make a safe and reliable server application, you'd better treat yourself as two people, a DBA, a coder (ASP coder,php Coder or others), many people just know: I'm doing a BBS, I'm doing a message book, I'm In Do a news release system, our process is like this, give the user a form, let the user submit, and then to write the database, use the time according to the conditions of the data records found out, and then display. Nothing, if you are an amateur, just want to write some small things to play, this is enough! If you want to take WebDev as your career, or you want to be a very professional amateur, you have to be a dba+coder, as to whether to be a designer depends on your ability and energy!
Well, by the way, I say so much, the thorough solution is to write your data operation plan on the DBMS, let the server know your intention before beginning execution, don't tell it rudely: I just want you to execute this command, don't ask me why!
The implementation of the method, the current more common, but also relatively easy to achieve is the stored procedures, the application of stored procedures can not only fundamentally solve the security problem of SQL injection, but also make your application speed multiplied (this increase may even reach an order of magnitude, which is related to many factors, It will also make the system you develop more like a large system, with a better architecture (such as the MVC pattern).
In MySQL 4.1.x and its subsequent versions and ODBC, a kind of thing called prepared statements is provided, which is essentially a stored procedure, a stored procedure that system presets (relative to user customization).