I want to build a webpage to connect to a MySQL database.
I can run queries from php or simply feed some arguments to a stored procedure and get the result.
SQL injection is one of my concerns. Does using stored procedures help me with securing my website?
I ask it because stored procedures require specific arguments and will not run if those requirements are not met. (Am I right?)
What are downsides of using stored procedures?
问题:
回答1:
What are downsides of using stored procedures?
Okay, you asked! :-)
I'm copying this from an answer I wrote on Quora a few years ago. I stand by the opinion.
I hardly ever use MySQL stored procedures, or recommend using them.
MySQL stored procedures are compiled the first time a session uses them — but the compiled version is discarded at the end of the session. Unlike stored procedures in Oracle or other RDBMS brands, which keep the compiled version persistently. This means that MySQL adds a lot of overhead to procedures, especially if your pattern is to call a procedure only once per session.
MySQL procedure language does not support packages or inheritance or other object-oriented features common in other RDBMS brands.
There are no debugging tools for MySQL procedures. There are a few tools that claim to offer debugging, but they are faking it. For example, by inserting logging statements into your procedure.
If you use rich programming editors or IDE's, I have not seen one that supports MySQL stored procedures. Iterative development is tedious.
MySQL does not have a rich library of functions or standard procedures to supplement basic functionality. You can find a few examples on the internet, but community contributions are pretty meager. Stored procedures never really caught on for MySQL.
Some people claim that stored procedures provide better security for developing applications, but there is no evidence of this. You can write insecure code with stored procedures. Likewise you can write secure code in any application language. The majority of SQL security problems come from SQL injection, and most cases of SQL injection are very easy to fix in your application if you use query parameters.
Stored procedures limit the scalability of your application, because they run in the database server. I consulted for a company that complained their MySQL Server was running at 100% CPU while their PHP application servers were idle. It was because they had implemented so much of their code in elaborate stored procedures.
I have occasionally seen stored procedures used well. The scenario where they provide most value is when you have a complex task to do with the database that would require copying large quantities of data from server to client and back again. By doing this task in a stored procedure, you can avoid a lot of network overhead.
回答2:
1. stored procedure queries are Belong to sql...on the other Hand literal SQL string needs time for compiling, parsing, syntax checking,etc...
2. Disadvantage of SP stored procedure's debugging is hard, stored procedure is not database independent.
or You can refer this link for more Info
https://blogs.msdn.microsoft.com/cisg/2008/09/09/sql-injection-are-stored-procedures-really-safe/