[SOLVED] Prevent SQL injection in ODBC and MS Access? Help!

[leesiulung]

I'm on a GoDaddy Windows hosting using PHP and MS Access.

 

It is well documented how to prevent SQL injection with MySQL, but how do one prevent SQL injection with ODBC and MS Access?

 

Any suggestions would be much appreciated. Seems like Access with PHP is fairly rare and not much information on the net is written about it.

[hitman6003]

Nearly all of the injection prevention is handled by php, not the database, so it shouldn't matter if you are using MySQL or Access.

 

Many times authors will recommend mysql_real_escape_string...you can use addslashes almost as effectively with ODBC since there is no equivalent, assuming magic_quotes_gpc isn't turned on.

 

http://www.php.net/addslashes

[leesiulung]

Is there not different vulnerabilities depending on the database and how it interprets certain characters though?

 

Anyhow, after using addslashes() to insert data into databse do you use stripslashes() to return to normal state?

[Crew-Portal]

Just dont allow users to insert charactors into forms such as * @ ! = & (){};'"`~/-+ and you should be fine against SQL Attacks!

[corbin]

Is there not different vulnerabilities depending on the database and how it interprets certain characters though?

 

Anyhow, after using addslashes() to insert data into databse do you use stripslashes() to return to normal state?

 

The data would be inserted in it's normal state.

 

For example, if you put in the following:

 

This is a string.  This is one with a \'.

 

It would be put into the database as:

 

This is a string.  This is one with a '.

 

Thus, when extracting you wouldn't need to convert anything ;p.

[NArc0t1c]

When it comes to cleaning a string, all SQL attacks is handled the same.

 

I would recommend htmlspecialchars, and urlencoding the data inputted into the database.

[Barand]

With MS SQL (and I suspect the same may also be the case with MS Access) you cannot escape quotes with a "\" as you do with MySQL.

 

You have to replace the single-quote with two single quotes, so where in MySQL you have

INSERT INTO table (name) VALUES ('O\'Reilly')

 

with MS you have

INSERT INTO table (name) VALUES ('O''Reilly')

[leesiulung]

So to summarize:

 

1. Remove all characters of the type:  * @ ! = & (){};'"`~/-+ or more accurately, only allow certain characters, but make sure those are not in the set of allowable characters

 

2. use htmlentities() and html_entity_decode()

 

3. use urlencoding() and urldecoding()

 

I'm not sure I understand why 2) and 3) above prevents SQL injection attacks?

 

Why do PHP not have something like mysql_real_escape_string() for Access? Certainly would be easier....

[Barand]

So to summarize:

 

1. Remove all characters of the type:  * @ ! = & (){};'"`~/-+ or more accurately, only allow certain characters, but make sure those are not in the set of allowable characters

 

So,

- No email addresses allowed

- Don't deal with any companies with names like "Marks & Spencer" , "Saachi & Saachi" or "Ford(UK) Ltd"

- Don't have any Irish contacts with names like O'Brien, O'Reilly etc

- Don't have any contact with those people like Tim Brooke-Taylor with hyphenated surnames

- And never any mathematical equations

[leesiulung]

So,

- No email addresses allowed

- Don't deal with any companies with names like "Marks & Spencer" , "Saachi & Saachi" or "Ford(UK) Ltd"

- Don't have any Irish contacts with names like O'Brien, O'Reilly etc

- Don't have any contact with those people like Tim Brooke-Taylor with hyphenated surnames

- And never any mathematical equations

 

To be honest, the reason I summarized those was that none of them seemed the correct way to handle it. In practice, one should not rely on a function intended for a different purpose be used for another purpose simply because it has the correct effect.

 

Thus, I think if I can get stored procedures to work it would  solve all these issues in one go. I believe this is the proper way of handling SQL injections in general.

 

It is just that PHP is a complete hack as a language and it isn't clear when and where you can use functions because frequently have caveats. Simply put, even after extensive testing I do not feel confident about my code.

[leesiulung]

Barand,

 

I'm getting pretty frustrated with PHP. It is the bane of my existence as I cannot stand the language. It is a love hate relationship. I love the fact that there is so much support for it, unfortunately the language in my personal opinion is terrible.

 

Anyhow, I have been unsuccessful in figuring out how to use stored procedures in MS Access using PHP. Can you or anyone help me out on how to sanitize the input for Access?

 

It should have been as easy as applying a function to the argument... here I am spending hours digging up information.

 

I would really appreciate it if anyone could help me. #!@$!@$@#%#@Q%#@TW$%^%&^%*$^#$#@%%@$!@

 

 

[Barand]

There are these functions

 

http://www.php.net/odbc_prepare

http://www.php.net/odbc_execute

 

for calling stored procedures. I do not know if their use is supported at the other end by Access

[leesiulung]

Barand,

 

Unfortunately, I'm not as familiar with stored procedures and could not get it to work. I read on a web page that Access do not support stored procedures, but who knows....

 

What things should I escape or disallow other than single quotes?

[Barand]

my usual sanitising function is

 

<?php
function clean($data)
{
    $res = get_magic_quotes_gpc() ? stripslashes($data) : $data;   
    $res = strip_tags($res);
    $res = mysql_real_escape_string($res);
    return $res;
}

 

I strip tags to prevent javascript redirects etc.

 

For Access, assuming I am right about the double single quote,

 

<?php
function clean($data)
{
    $res = get_magic_quotes_gpc() ? stripslashes($data) : $data;   
    $res = strip_tags($res);
    $res = str_replace ("'", "''", $res);
    return $res;
}

 

So when the data is posted from a form

 

<?php
foreach ($_POST as $k=>$v)
{
     $$k = $clean($v);
}

[leesiulung]

Barand,

 

I am assuming that there is no need to reverse the process?

 

 

For Access it is indeed double quotes to escape a single quote.

 

 

 

[Barand]

Only if you want to display posted data on the page, in which case you want to print

 

"O'Reilly" and not "O''Reilly"

 

But that's the same problem with magic_quotes anyway.

[leesiulung]

Barand,

 

I'm not sure I understand. 

 

When pulling the date from the database, what should I do? Do I need to revert what was done when inserting the data into the database?

 

Please elaborate.

 

 

[Barand]

The data in the db should be OK

 

Input : It's

 

Query : INSERT INTO table (word) VALUES ('It''s')

 

Stored as : It's

 

SELECT word FROM table --> It's

 

echo $row['word'] --> It's

[leesiulung]

Thanks!

[leesiulung]

Barand's clean() function for Access worked like a charm. Tested it on GoDaddy's servers.

 

Thanks Barand!!! I very much appreciate your help.