Can a parameterized statement stop all SQL injection?

Question

If so, why are there still so many successful SQL injections? Just because some developers are too dumb to use parameterized statements?

Answer 1

The links that I posted in my comments on the question very well explain the problem. I summed up my feelings about why the problem persists, below:

• Those who are just starting out may not be aware of SQL injection.

• Some of them are aware of SQL injection, but they believe that escaping is (only?) A solution. If you do a quick Google search php mysql query , the first page will appear on the mysql_query page, which has an example that shows the interpolation of escaped user input into the query. It does not mention (at least not what I see) the use of ready-made instructions. As others have said, there are so many tutorials that use parameter interpolation that it’s not really surprising how often it is still used.

• Lack of understanding of how parameterized operators work. Some people think it's just a fantastic way to escape values.

• Others are aware of parameterized statements, but do not use them because they have heard that they are too slow. I suspect that many people have heard incredibly slow phrasal phrases, but have not actually conducted any tests of their own. As Bill Carwin noted in his speech, performance differences should rarely be used as a factor when considering the use of prepared statements. The benefits of preparing once, performing many, often seem to be forgotten, as well as improving the security and reliability of the code.

• Some use parameterized statements everywhere, but with interpolation of unverified values, such as table and column names, keywords, and conditional statements. The main examples of this are dynamic searches, such as those that allow users to specify several different search fields, comparison conditions, and sort order.

• False sense of security when using ORM. ORMs still allow interpolation of parts of an SQL statement - see 5.

• Programming is a big and complicated question; database management is a big and complex question; security is a big and complex question. Developing a secure database application is not easy - even experienced developers can be caught.

• Many stackoverflow answers do not help. When people write questions that use dynamic SQL and parameter interpolation, often there is a lack of answers that involve the use of parameterized statements. In some cases, I have had people refuting my suggestion to use prepared statements - usually because of perceived unacceptable office expenses. I seriously doubt that those who ask most of these questions are in a position where the extra few milliseconds taken to prepare a parameterized statement will have a catastrophic effect on their application.

Answer 2

When articles talk about parameterized queries that stop SQL attacks, they don’t actually explain why, often this is the case “He does, so don’t ask why,” perhaps because they don’t know. A sure sign of a bad teacher is one who cannot admit that he knows nothing. But I was distracted. When I speak, I realized that it is completely clear that they simply confuse me. Imagine a dynamic SQL query

 sqlQuery='SELECT * FROM custTable WHERE User=' + Username + ' AND Pass=' + password 

so a simple sql injection was just to enter the username as "OR 1 = 1 - This will really make the sql query:

 sqlQuery='SELECT * FROM custTable WHERE User='' OR 1=1-- ' AND PASS=' + password 

This suggests that all the clients they have are empty ('') or 1 = 1, which is logical, equating true. He then uses - to comment on the rest of the request. Thus, it will simply print the entire client table or do whatever you want with it, when you log in, it will log in with the rights of the first user, which can often be an administrator.

Now parameterized queries do it differently, with code like:

 sqlQuery='SELECT * FROM custTable WHERE User=? AND Pass=?' parameters.add("User", username) parameters.add("Pass", password) 

where username and password are variables pointing to the associated username and password entered

Now, perhaps you think this is not changing anything. Of course, you can still just enter something like Nobody OR 1 = 1 '- into the username field, effectively executing the query:

 sqlQuery='SELECT * FROM custTable WHERE User=Nobody OR 1=1'-- AND Pass=?' 

And that seems like a valid argument. But you are mistaken.

The way to work with parameterized queries is that sqlQuery is sent as a query, and the database knows exactly what this query will do, and only after that it will insert the username and passwords just as values. This means that they cannot complete the request because the database already knows what the request will do. Thus, in this case, he will look for the username "Nobody OR 1 = 1" - ") and an empty password, which should look false.

This is not a complete solution, and input validation still needs to be done, as it will not affect other issues such as XSS attacks, since you can still put javascript in the database. Then, if it is read on the page, it will display it as normal javascript, depending on some validation check. So the best thing is still to use input validation, but using parameterized queries or stored procedures to stop any SQL attacks.

Answer 3

Well, a good question. The answer is rather stochastic than deterministic, and I will try to explain my opinion with a small example.

There are many links on the network that suggest that we use parameters in our queries or use a stored procedure with parameters to avoid SQL injection (SQLi). I will show you that stored procedures (for example) are not a magic wand against SQLi. Responsibility remains with the programmer.

Consider the following SQL Server stored procedure, which retrieves a user row from the Users table:

 create procedure getUser @name varchar(20) ,@pass varchar(20) as declare @sql as nvarchar(512) set @sql = 'select usrID, usrUName, usrFullName, usrRoleID '+ 'from Users '+ 'where usrUName = '''+@name+''' and usrPass = '''+@pass+'''' execute(@sql) 

You can get the results by passing username and password as parameters. Assuming the password is in free text (just for the sake of simplicity of this example), a normal call would be:

 DECLARE @RC int DECLARE @name varchar(20) DECLARE @pass varchar(20) EXECUTE @RC = [dbo].[getUser] @name = 'admin' ,@pass = '!@Th1siSTheP@ssw0rd!!' GO 

But here we have a bad programming technique used by the programmer inside the stored procedure, so an attacker can do the following:

 DECLARE @RC int DECLARE @name varchar(20) DECLARE @pass varchar(20) EXECUTE @RC = [TestDB].[dbo].[getUser] @name = 'admin' ,@pass = 'any'' OR 1=1 --' GO 

The above parameters will be passed as arguments to the stored procedure, and the SQL command that will ultimately be executed:

 select usrID, usrUName, usrFullName, usrRoleID from Users where usrUName = 'admin' and usrPass = 'any' OR 1=1 --' 

... which will return all rows to users

The problem here is that even we follow the principle of "Create a stored procedure and pass the fields to search as parameters", SQLi is still running. This is because we simply copy our bad programming practice into a stored procedure. The solution is to rewrite our stored procedure as follows:

 alter procedure getUser @name varchar(20) ,@pass varchar(20) as select usrID, usrUName, usrFullName, usrRoleID from Users where usrUName = @name and usrPass = @pass 

I'm trying to say that developers should first find out what SQLi attack is and how it can be executed, and then defend their code accordingly. Blindly following “best practices” is not always a safer way ... and perhaps that is why we have so many “best practices” - failures!

Answer 4

Yes, using prepared statements stops all SQL injections, at least theoretically. In practice, parameterized operators may not be real prepared operations, for example. PHP's PDO mimics them by default to open an edge attack.

If you use real prepared statements, everything is in order. Well, at least until you combine unsafe SQL into your query, as a reaction to the inability to prepare table names, for example.

If so, why are there still so many successful SQL injections? Just because some developers are too dumb to use parameterized statements?

Yes, the focus here is on education and legacy codes. Unfortunately, many textbooks use shielding, and, unfortunately, they cannot be easily removed from the Internet.

Answer 5

I avoid absolutes in programming; there is always an exception. I highly recommend stored procedures and command objects. Most of my database is with SQL Server, but I play with MySql from time to time. There are many benefits to stored procedures, including cached query plans; yes, this can be achieved with parameters and embedded SQL, but it opens up more possibilities for injection attacks and does not help with the separation of problems. It is also much easier for me to ensure the security of the database, since my applications usually only have permission to execute these stored procedures. Without direct access to the table / view, it is much more difficult to enter anything. If the application user is compromised, he only has permission to do exactly what was determined in advance.

My two cents.

Answer 6

I would not say stupid.

I think textbooks are a problem. Most SQL tutorials, books, anything that explains SQL with embedded values, don't mention binding parameters at all. People studying these tutorials do not have the opportunity to learn it correctly.

Answer 7

Since most of the code is not written with security and control in mind, given the choice between adding features (especially something visible that can be sold) and security / stability / reliability (which is much harder to sell), they will almost invariably choose the first. Security is only a problem when it becomes a problem.

Answer 8

Can a parameterized statement stop all SQL injections?

Yes, if your database driver offers a placeholder for every possible SQL literal. Most prepared drivers do not have instructions. Say you never find a placeholder for a field name or an array of values. This will force the developer to lean back to send the request manually, using concatenation and manual formatting. With a predicted result.

That's why I made my Mysql wrapper for PHP, which supports most of the literals that can be added to the query dynamically, including arrays and identifiers.

If so, why are there still so many successful SQL injections? Just because some developers are too dumb to use parameterized statements?

As you can see, it is actually simply not possible to configure all your requests, even if you are not dumb.

Answer 9

First, my answer to your first question: Yes, as far as I know, using parameterized queries, SQL injections will no longer be possible. As for your next questions, I'm not sure and can only express my opinion for reasons:

I think it’s easier to “just” write the SQL query string, combining some different parts (possibly even depending on some logical checks) along with the values ​​to be inserted. It simply creates a request and executes it. Another advantage is that you can print (echo, output, or something else) the sql query string, and then use this string to manually query the database engine.

When working with prepared statements, you always have at least one step: you must build your request (including parameters, of course) you must prepare the request on the server you must bind the parameters to the actual values ​​that you want to use for your request you must complete the request.

This works a little more (and not so easy to program), especially for some "fast and dirty" tasks, which often turn out to be very durable ...

Respectfully,

Insert

Answer 10

To protect your application from SQL injection, follow these steps:

Step 1. Limit the input. Step 2. Use parameters with stored procedures. Step 3. Use parameters with dynamic SQL.

Refer to http://msdn.microsoft.com/en-us/library/ff648339.aspx

Answer 11

SQL injection is a subset of the larger code entry problem, where data and code are provided on the same channel, and the data is wrong for the code. Parameterized queries prevent this by generating a query using a context about what data is and what code is.

In some specific cases, this is not enough. In many DBMSs, you can dynamically execute SQL with stored procedures, presenting an SQL injection error at the DBMS level. Calling such a stored procedure using parameterized queries will not prevent the use of SQL injection in the procedure. Another example can be seen in this blog post .

Most often, developers misuse this feature. Typically, the code looks something like this when executed correctly:

 db.parameterize_query("select foo from bar where baz = '?'", user_input) 

Some developers concatenate the strings together and then use a parameterized query that does not actually make the above data / code differentiation, which provides the security guarantees that we are looking for:

 db.parameterize_query("select foo from bar where baz = '" + user_input + "'") 

The proper use of parameterized queries provides very strong but impenetrable protection against SQL injection attacks.

Answer 12

even if prepared statements are correctly used in all web code applications, SQL injection flaws can still exist if the database code components construct user input queries in an unsafe manner. The following is an example of a stored procedure that is vulnerable to SQL. embedding in the @name parameter:

 CREATE PROCEDURE show_current_orders (@name varchar(400) = NULL) AS DECLARE @sql nvarchar(4000) SELECT @sql = 'SELECT id_num, searchstring FROM searchorders WHERE ' + 'searchstring = ' + @name + '; EXEC (@sql) GO 

Even if the application passes the user-entered name value to the stored procedure in a safe manner, the procedure itself combines this directly into a dynamic request and, therefore, is vulnerable.