How to prevent SQL injection in my script? 2

[Mizugori]

I have a perl program which allows the user to enter a value into a web form and then when they click submit, that value is used to look up a user ID and the data associated with it in a PostgreSQL database.

My question is, how do I prevent them from entering special characters with the intention of hacking and such?

thanks!

 

[feherke]

Hi

Escape all single quotes ( ' ) and backlashes ( \ ) :

$string_from_the_form=~s/(['\\])/\\$1/g

Feherke.

http://rootshell.be/~feherke/

 

[ishnid]

Read up on using prepared statements with placeholders and bound variables. Here's a great article. Doing that lets DBI take care of quoting everything for your rather than doing it yourself (which can lead to maintainability problems if you ever change database, for example).

 

[Mizugori]

thanks... so i just want to make sure i understand because i'm pretty new to perl; is that line of code going to change the data stored in the string? ie will it remove any special characters and still allow the variable to be sent to SQL, or do i need to make some kind of if statement to make sure it is not allowed to go through if it contains those characters?

sorry!

 

[ishnid]

I wouldn't go with a hand-rolled solution like that. Firstly, you have to be absolutely sure you're eliminating any characters that could be problematic. Off the top of my head, semicolons are already missing. There could well be more. Use the prepared statements with the bind parameters to get DBI to do it for you. That's the recommended way to go.

 

[feherke]

Hi

I used PG.pm, not DBI. ( A quite old one to be honest. ) That is why I always escaped "by hand".

will it remove any special characters

You do not have to remove anything, only to escape the characters which could change the SQL command's meaning.

Off the top of my head, semicolons are already missing.

What is wrong with semicolons ( ; ) ?

Feherke.

http://rootshell.be/~feherke/

 

[stevexff]

Consider the following. You have

$dbh->do("Select * from mytab where a = $userinput");

Your pernicious user types in

33;DELETE FROM myTab

DBI does what it's told, and your database is now empty...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[MillerH]

Hence why it's good to listen to ishnid here and just use placeholders and prepared statements.

- Miller

 

[feherke]

Hi

So that is. I do not think the problem there is the semicolon ( ; ) because even if you escape or remove it you will not have correct SQL statement.

And what the database module would do with that if you use prepared statement ? I think that is just invalid data. And although taking care of SQL injection is the database module's task too, taking care of invalid data is not the database module's task.

Feherke.

http://rootshell.be/~feherke/

 

[ishnid]

An invalid SQL statement is better than a valid one that does something malicious, no?

If you have that semicoloned example in a prepared statement, it will be quoted as a string, according to which DBD you're using (and of course that changes from database to database, so if you ever switch to another DBMS you'll have to re-examine the characters you escaped, rather than just changing the driver you're using).

It's worth noting that at this point in time, the PostgreSQL driver won't actually execute multiple statements at once. Sybase and Microsoft's SQL Server are mentioned as being ones that do, so if you ever change to use those you'd have to be careful.

When you speak of taking care of invalid data, I'd say you have to divide that into two types of invalid data. There's data that's invalid in that it breaks your SQL (through an injection attack) and there's data that doesn't make sense in the context of what you're storing in your database. You should let your database driver take care of the first, by using prepared statements and bind parameters. It's easy to do and there's no reason not to. The second type is up to you, depending on what your data is supposed to represent. Obviously "4; DELETE FROM users" isn't a valid email address, for example.

 

[feherke]

Hi

You should let your database driver take care of the first, by using prepared statements and bind parameters. It's easy to do and there's no reason not to.

I have one. I hate to debug prepared statements. You can not see their final form just the bunch of placeholders.

Obviously "4; DELETE FROM users" isn't a valid email address, for example.

No it is not, but that is just logic. An e-mail address is a string and that is a valid string. And there is no security risk in writing that as a string into the database. ( If is not written as a string, I think there is no need for attacker against that database. )

Feherke.

http://rootshell.be/~feherke/

 

[audiopro]

Thanks Ishnid - that article was very informative.
I regex all of my incoming data to allow only alpha and numeric chars into the script. The only additional char allowed is '@' but only in the event of email references.
I use prepared statements so assume the form input is just treated as data. Are there still any sneaky little risks which I should be wary of?
I do have a few of my own tests which I put the form inputted data through but I am looking to be as secure as possible. Considering the data which I am working with, there would be little to gain from hacking it but the effect on the various small businesses I work with could be very serious indeed.
I have tried to make it so that if a hack is attempted, nothing out of the ordinary happens. No alarm bells, whistles or alert screens just return to the index page as if nothing has happened. Is this a good approach?

Keith

http://www.studiosoft.co.uk

 

[brigmar]

No alarm bells, whistles or alert screens"

A silent alarm is always good:
I log/email "unexpected" behavior, so for example, instead of my SQL statements just dying, they will report what statement died and the parameters it was passed. Not to the user, but in a log and to an administrative email that I have access to. This helps in a few ways.

In conjunction with the HTTP logs, they help track down genuine issues with your code/processes.

It gives me a heads-up to some issues before my client receives complaints.