1

Any way to prevent malicious sql statements without using prepared statements and parameterized queries?

Example after simplify: 

<?php                
    $con = mysqli_connect($_POST['db_server'], $_POST['db_user'], 
                      $_POST['db_password'], $_POST['db_database']) or die(mysql_error());

    $result = mysqli_query($con, $_POST['query_message']);            
?>

Is it possible to check out the parameter $_POST['query_message'] is safe or not?

Community
  • 1
  • 1
user3172211
  • 51
  • 1
  • 5
  • 2
    Now this one is definitely itchin' for a quick shot of an [`SQL injection`](http://stackoverflow.com/q/60174/) - *"is safe or not?"* - **NOT** - You stand at one major attack, then it'll be your "heart". – Funk Forty Niner Mar 11 '14 at 03:51

4 Answers4

2

Why you don't wanna use Prepared Statements ? That is really weird. I strongly suggest you should go for it.

You could make use of mysqli::real_escape_string for escaping quotes that is commonly used for SQL Injection Attacks.

Something like...

OOP Style

$message = $mysqli->real_escape_string($_POST['query_message']);

Procedural Style

$message = mysqli_real_escape_string($link,$_POST['query_message']);
Community
  • 1
  • 1
Shankar Narayana Damodaran
  • 68,075
  • 43
  • 96
  • 126
  • Because prepared statements is less flexible, I would like to provide a more generic script to apply the task of CRUD(looks like it is impossible?) – user3172211 Mar 11 '14 at 03:58
  • Heh, we made the same edits at the same time, Shankar. xD And @user3172211, it's no less flexible than building the statement up with fragments manually. In fact, doing the latter will likely leave you prone to SQL injection attacks, even with `real_escape_string()` involved. – Benjamin Nolan Mar 11 '14 at 03:59
  • PDO is less flexible ?? Can you explain ? @user3172211 – Shankar Narayana Damodaran Mar 11 '14 at 04:00
  • Sorry, Shankar, it should be "parameterized" queries is less flexible. – user3172211 Mar 11 '14 at 04:04
  • @user3172211, I still don't have an idea why you state them that they are not flexible :) . Actually they are more readable than your typical `queries`.. because you don't have the neccessity to escape each and every parameter.. everything is taken care of it. There are some readily available classes to get you started with. Here goes one https://github.com/indieteq/PHP-MySQL-PDO-Database-Class – Shankar Narayana Damodaran Mar 11 '14 at 04:10
2

You should always build your queries within your code and then sanitise any variables you're going to use within them. NEVER pass the query or the database connection variables in via $_POST unless your user is querying the database via that form, in which case I'd recommend you just install phpMyAdmin.

As for sanitising your variables, if you really don't want to use PDO's prepared statements, you can sanitise incoming integers as follows:

$id = (isset($_POST['id']) ? (int)$_POST['id'] : null);
if ($id) {
    $sql = "SELECT *
            FROM `table`
            WHERE `id` = {$id}";
}

And for strings use this:

$username = (isset($_POST['username']) ? mysqli_real_escape_string($con, $_POST['username']) : null);
if ($username) {
    $sql = "SELECT *
            FROM `table`
            WHERE `username` = {$username}";
}

You can also call real_escape_string() directly on your $con object as follows:

$username = (isset($_POST['username']) ? $con->real_escape_string($con, $_POST['username']) : null);

However, as with @Shankar-Damodaran above, I highly suggest you do use PDO prepared statements to query your database.

Benjamin Nolan
  • 1,170
  • 1
  • 10
  • 20
0

other way is using:

htmlentities($query);

as an extra you could use preg_match() regular expressions to avoid the inclusion of certain words (SELECT, DROP, UNION .......)

Example: 

try{
  $query = sprintf("SELECT * FROM users WHERE id=%d", mysqli_real_escape_string($id));
  $query = htmlentities($query);
  mysqli_query($query);
}catch(Exception $e){
    echo('Sorry, this is an exceptional case');
}
felixmpa
  • 2,028
  • 1
  • 15
  • 16
0

There are real world cases where prepared statements are not an option.

For a simple example, a web page page where you can do a search on any number of any columns in the database table. SAy that table has 20 searchable columns. you would need a huge case statement that has all 20 single column queries, all 19+18+17+16+15+14+13+... 2 column queries, all possible 3 column queries... that's a LOT of code. much less to dynamically construct the where clause. That's what the OP means by prepared statements being less flexible.

Simply put, there is no generic case. If there was, php would have it already.

real_escape_string can be beaten. a common trick is to % code the character you are trying to escape so real_escape_string doesn't see it. then it gets passed to mysql, and decoded there. So additional sanitizing is still required. and when all characters used in injection are valid data, it's a PITA, because you can't trust real_escape_string to do it.

If you are expecting an integer, it's super easy.

$sanitized=(int)$unsanitized;

done.

If you are expecting a small text string, simply truncating the string will do the trick. does't matter that it's not sanitized if there's not enough room to hold your exploit

But there is no one size fits all generic function that can sanitize arbitrary data against sql injection yet. If you write one, expect it to get put into php. :)

Chuck Cochems
  • 31
  • 1