0

I know there are many questions out there already regarding this subject, however none I have found specifically answer my question.

I have created a simple PHP function that validates all user supplied input. Given the nature of the application I am developing, it is rare that the use of any characters other than numbers and letters are required. Occasionally commas (,), hyphens (-), ampersands (&) and single-quotes ('). My PHP function ensures that all input only contains these characters, and then adds a single backslash in front of each instance of one of these symbols.

I am assuming that if I guarantee that all input is sent through this function before being used in a MySQLi query, I am safe from SQL injection and have no need to use prepared statements, parameterised queries etc, regarding security only.

Am I correct?

user3054325
  • 29
  • 3
  • 1
    Can we ask why you *want* to avoid parameterised queries? – Matt Gibson Jan 11 '15 at 16:33
  • Parameterized queries have another benefit: the database engine can optimize and cache the query plan when the same query is used many times, so the performance improves. Why not use them? You are having more work and risking yourself with an unsecure solution. – Zanon Jan 11 '15 at 16:36
  • 1
    user3054325 like @Zanon points to you there are a number of reasons that are in your favor to use prepared statements. –  Jan 11 '15 at 16:40
  • 1
    Parameterized queries were not invented to _prevent SQL injections_, it has many benefits as Zanon commented and one of them is to prevent the general idea of SQL injection. – dbf Jan 11 '15 at 16:45
  • @MattGibson, the reason I wish to not use them, is because I would like to have my application running asap. The only way I see to be able to implement such queries is by installing mysqlnd. Something I haven't a clue how to do, even after researching all over the internet. I am on a mac and you need to use terminal with lots of commands I don't understand. Otherwise I can use what I am currently using, bind the results to variables and use a mysqli_stmt_fetch while loop to retrieve the results. This works, but the downside to this is I can't have another query within the loop, which I need. – user3054325 Jan 13 '15 at 14:56
  • @user3054325 There are plenty of solutions out there that will let you get a whole Apache/PHP/MySQL stack installed on a Mac pretty trivially, and for free. [MAMP](http://www.mamp.info/en/) is probably the best-known example. You only really need to get into command-line stuff if you want to build extra modules against the PHP that ships "built-in" with OS X—but you don't have to use that one if you don't want to. I really recommend sorting your development environment out so you can use modern practices, rather than avoiding them because of your environment. – Matt Gibson Jan 13 '15 at 15:08
  • @MattGibson thank you for your reply. I am using MAMP/Apache/PHP 5.4.3, however in the PHP info there is no mention of mysqlnd anywhere. The function mysqli_stmt_get_result() is not recognized. After looking online I found a statement somewhere saying that mysqlnd is shipped by default with PHP 5.3+ on a windows environment (WAMP), prompting me to believe that is not the case on MAMP...? – user3054325 Jan 14 '15 at 13:34
  • You should still be able to use parameterised queries, and prepared statements, just fine. `mysqli_stmt_get_result()` is for a specific type of prepared statement result fetch, which has nothing to do with whether the query is parameterised. If you want to avoid the need for mysqlnd, you should use `mysqli_stmt_bind_result()` instead. – Matt Gibson Jan 14 '15 at 14:27

2 Answers2

3

tl;dr NO

There are many attack vectors and many different kinds of SQL Injection out there. For instance, use the wrong method from the MySQLi class and you're open to many attacks.

Just don't even bother thinking about building your own stuff, you'll fail, you'll fail hard. Follow best practices and concentrate on other parts while becoming the most awesome programmer this world has ever seen.

On a last note, prepared statements are offering you many benefits. Although the caching of queries as mentioned by @Zanon is sadly not one of them if you use MySQL (it would be with e.g. PostgreSQL). But that is up to the documentations of the software (and their exact versions) you use.

Fleshgrinder
  • 15,703
  • 4
  • 47
  • 56
0

It seems from our discussion in the comments that the underlying reason you want to avoid parameterised queries is because you couldn't get mysqli_stmt_get_result() working under MAMP as it requires the mysqlnd native driver.

In fact, that has no bearing on your ability to use parameterised queries or prepared statements. It's an enhancement to the normal method of fetching bound results from prepared statements instead of the older (but still standard and perfectly-supported) mysqli_stmt_bind_result(), which will work fine without mysqlnd.

Please use parameterised queries. If you also want to use prepared statements with bound result variables without the mysqlnd driver, use mysqli_stmt_bind_result() instead of mysqli_stmt_get_result().

If you have any problems using parameterised queries, please post a new question with a complete, minimal example of the code you're having problems with.

Matt Gibson
  • 37,886
  • 9
  • 99
  • 128
  • Thanks again, but my problem using mysqli_stmt_bind_result() is that you cannot execute another query within a while loop whilst using mysqli_stmt_fetch() to retrieve the results using this method. – user3054325 Jan 15 '15 at 13:46