May 20, 2014 at 4:18 pm
Hi, I have a website which has a link where anyone can type in an SQL query and once he/she clicks submit it will go to a CGI script in perl which will then connect with the SQL database and read the query. However, in order to prevent injections I want to use the bind_param value but in the script below, I tried incorporating it and then went back to the website to test it out and I got an error: Search pattern not terminated at /home/ls/adrian/public_html/cgi-bin/sql_query.cgi line 20
Please help, thank you:
CGI Script
#!/usr/bin/perl -w
use strict;
use DBI;
use CGI;
use CGI::Carp('fatalsToBrowser');
if ($query->param('submit')){
my $ud_query = $query->param('enter_query');
my $db_query = "$ud_query";
#my $sth = $dbh->prepare($db_query);
my $sth = $dbh->prepare(?);
$sth->bind_param( 1, $db_query );
$sth->execute();
my$total = $sth->rows;
my @names = @{$sth->{NAME}};
my $names;
my$s;
print "<table border=1>
<tr>";
foreach $names(@names) {
my$names = uc($names);
print "<th>$names</th>";
}
print "</tr>";
while (my@row = $sth->fetchrow_array()){
print "<tr>";
foreach $s(@row) {
print "<td>$s</td>";
}
print "</tr>";
}
print "<tr>
<th>TOTAL</th>
<th>$total</th>
</tr>";
print "</table>";
}
May 21, 2014 at 10:53 am
This is a Microsoft SQL Server forum, and doubt you'll get much DBI help.
None the less, if you look at the man page for DBI, you'll see an example of bind_param:
$dbh->{RaiseError} = 1; # save having to check each method call
$sth = $dbh->prepare("SELECT name, age FROM people WHERE name LIKE ?");
$sth->bind_param(1, "John%"); # placeholders are numbered from 1
$sth->execute;
DBI::dump_results($sth);
so at the very least you'd want to put that ? in quotes, and even then I'm betting its gonna flunk the prepare because just having a parameter is either going to be not valid syntax or if it is, the escaping process could play havok with the sql statement you're trying to pass in. According to my reading, bind_param isn't really meant to pass through arbitrary sql query text, its meant to PREVENT passing arbitrary sql query text to the database being used.
But really, trying to pass a complete sql statement supplied by the user and trying at the same time to prevent sql injection are pretty much mutually exclusive goals. Binding parameters are all about preventing execution of arbitrary sql strings BUT LOOK! You want to execute an arbitrary sql string in your code!
May 21, 2014 at 12:52 pm
You can test the result set, if the count is 0 then don't execute the code, see the code!
😎
DECLARE @RES TABLE
(
is_hidden BIT NULL
,column_ordinal INT NULL
,name SYSNAME NULL
,is_nullable BIT NULL
,system_type_id INT NULL
,system_type_name SYSNAME NULL
,max_length INT NULL
,precision INT NULL
,scale INT NULL
,collation_name SYSNAME NULL
,user_type_id INT NULL
,user_type_database SYSNAME NULL
,user_type_schema SYSNAME NULL
,user_type_name SYSNAME NULL
,assembly_qualified_type_nameSYSNAME NULL
,xml_collection_id INT NULL
,xml_collection_database SYSNAME NULL
,xml_collection_schema SYSNAME NULL
,xml_collection_name SYSNAME NULL
,is_xml_document BIT NULL
,is_case_sensitive BIT NULL
,is_fixed_length_clr_type BIT NULL
,source_server SYSNAME NULL
,source_database SYSNAME NULL
,source_schema SYSNAME NULL
,source_table SYSNAME NULL
,source_column SYSNAME NULL
,is_identity_column BIT NULL
,is_part_of_unique_key BIT NULL
,is_updateable BIT NULL
,is_computed_column BIT NULL
,is_sparse_column_set BIT NULL
,ordinal_in_order_by_list BIT NULL
,order_by_is_descending BIT NULL
,order_by_list_length BIT NULL
,tds_type_id INT NULL
,tds_length INT NULL
,tds_collation_id INT NULL
,tds_collation_sort_id INT NULL
)
INSERT INTO @RES
EXEC sp_describe_first_result_set N'SELECT * FROM SYS.OBJECTS'
SELECT COUNT(*) FROM @RES
May 22, 2014 at 7:30 am
Could I ask what the purpose of this is? I hope the code will run under a severely restricted account at the least.
May 22, 2014 at 8:12 am
Eirikur Eiriksson (5/21/2014)
You can test the result set, if the count is 0 then don't execute the code, see the code!😎
DECLARE @RES TABLE(
is_hidden BIT NULL
,column_ordinal INT NULL
,name SYSNAME NULL
,is_nullable BIT NULL
,system_type_id INT NULL
,system_type_name SYSNAME NULL
,max_length INT NULL
,precision INT NULL
,scale INT NULL
,collation_name SYSNAME NULL
,user_type_id INT NULL
,user_type_database SYSNAME NULL
,user_type_schema SYSNAME NULL
,user_type_name SYSNAME NULL
,assembly_qualified_type_nameSYSNAME NULL
,xml_collection_id INT NULL
,xml_collection_database SYSNAME NULL
,xml_collection_schema SYSNAME NULL
,xml_collection_name SYSNAME NULL
,is_xml_document BIT NULL
,is_case_sensitive BIT NULL
,is_fixed_length_clr_type BIT NULL
,source_server SYSNAME NULL
,source_database SYSNAME NULL
,source_schema SYSNAME NULL
,source_table SYSNAME NULL
,source_column SYSNAME NULL
,is_identity_column BIT NULL
,is_part_of_unique_key BIT NULL
,is_updateable BIT NULL
,is_computed_column BIT NULL
,is_sparse_column_set BIT NULL
,ordinal_in_order_by_list BIT NULL
,order_by_is_descending BIT NULL
,order_by_list_length BIT NULL
,tds_type_id INT NULL
,tds_length INT NULL
,tds_collation_id INT NULL
,tds_collation_sort_id INT NULL
)
INSERT INTO @RES
EXEC sp_describe_first_result_set N'SELECT * FROM SYS.OBJECTS'
SELECT COUNT(*) FROM @RES
Can you explain what this is doing? I don't get it. You say if the count is zero, don't run the code...but the code has already been executed. More importantly I don't understand what this is trying to check. Maybe there is something I am not seeing though.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 22, 2014 at 8:16 am
May 22, 2014 at 8:28 am
Quite interesting...are they creating temporary databases and users programatically with very restricted rights to that and only that database and assures they cannot elevate or access any other database then dropping the db and user after a session timeout? I tried briefly to to some "bad things" on it....will have to take a look when I have more time....
May 22, 2014 at 8:37 am
david.john.gilligan (5/22/2014)
Quite interesting...are they creating temporary databases and users programatically with very restricted rights to that and only that database and assures they cannot elevate or access any other database then dropping the db and user after a session timeout? I tried briefly to to some "bad things" on it....will have to take a look when I have more time....
Apparently a variety of things including the temporary databases, the "about" page talks about it in general and has some specifics according to which server vendor and version the user selects.
May 22, 2014 at 8:42 am
patrickmcginnis59 10839 (5/22/2014)
david.john.gilligan (5/22/2014)
Quite interesting...are they creating temporary databases and users programatically with very restricted rights to that and only that database and assures they cannot elevate or access any other database then dropping the db and user after a session timeout? I tried briefly to to some "bad things" on it....will have to take a look when I have more time....Apparently a variety of things including the temporary databases, the "about" page talks about it in general and has some specifics according to which server vendor and version the user selects.
Now I am more interested, although I really dislike SO, from their site:
"You want help with a tricky query, and you'd like to post a question to a Q/A site like StackOverflow. Build a representative database (schema and data) and post a link to it in your question. Unique URLs for each database (and each query) will be generated as you use the site; just copy and paste the URL that you want to share, and it will be available for anyone who wants to take a look. They will then be able to use your DDL and your SQL as a starting point for answering your question. When they have something they'd like to share with you, they can then send you a link back to their query. "
Not a half bad idea at all....so the database does persist after a session. Useful.
May 22, 2014 at 10:21 am
Sean Lange (5/22/2014)
Can you explain what this is doing? I don't get it. You say if the count is zero, don't run the code...but the code has already been executed. More importantly I don't understand what this is trying to check. Maybe there is something I am not seeing though.
The code has not been executed and the server throws an error if the statement is invalid or fails static analysis.
The check here is for the metadata of the result set, count of 0 means there is no structure, hence there is no point in executing the query. A further check could be added such as the existence of certain columns in the result set.
A try/catch would further enhance this by i.e. return an empty result set instead of an error message or an empty metadata structure
This procedure and the dmv sys.dm_exec_describe_first_result replace the depreciated "SET FMTONLY".
😎
May 22, 2014 at 10:23 am
I don't understand the reply with the code and if it results in 0. I just want to know how to use placeholders in my code above in order to prevent injections. I have a placeholder with question mark, but doesn't work.
May 22, 2014 at 10:52 am
Eirikur Eiriksson (5/22/2014)
Sean Lange (5/22/2014)
Can you explain what this is doing? I don't get it. You say if the count is zero, don't run the code...but the code has already been executed. More importantly I don't understand what this is trying to check. Maybe there is something I am not seeing though.The code has not been executed and the server throws an error if the statement is invalid or fails static analysis.
The check here is for the metadata of the result set, count of 0 means there is no structure, hence there is no point in executing the query. A further check could be added such as the existence of certain columns in the result set.
A try/catch would further enhance this by i.e. return an empty result set instead of an error message or an empty metadata structure
This procedure and the dmv sys.dm_exec_describe_first_result replace the depreciated "SET FMTONLY".
😎
I am missing something. The thread was to try to prevent sql injection. I don't see how this would help with that at all.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 22, 2014 at 10:53 am
aajavaherian (5/22/2014)
I don't understand the reply with the code and if it results in 0. I just want to know how to use placeholders in my code above in order to prevent injections. I have a placeholder with question mark, but doesn't work.
I think you have to look closer into the meaning of an "SQL injection attack" which you say you are trying to prevent. Theres two points to take away here, #1, passing SQL that you received from a web form to DBI is an example of ALLOWING SQL INJECTION and #2, bind_param is apparently designed to PREVENT SQL INJECTION, so you're essentially trying to do contradictory things, you are feeding arbitrary SQL provided via a web form to a function "bind_param" that is designed to prevent arbitrary SQL from being passed to DBI.
I hate to point to a SO thread as I know its sort of disliked here, but this fellow also gets a similar explanation and he's just trying to pass a fragment:
http://stackoverflow.com/questions/2068789/how-to-escape-a-native-sql-function-in-perl-bind-param
Obviously if I am mistaken in interpretting your situation, my apologies!
edit: I don't really understand how to apply Eirikur Eiriksson's post in relation to your situation but gotta admit, I'm still saving his post for future reference for describing result sets! Its possible that you'd save his routine to a stored procedure and pass your SQL to a routine that runs it against his stored procedure and then examine the results, but thats still not going to let you pass it through "bind_param" like you're trying to do.
May 22, 2014 at 1:20 pm
First of all, sorry for my hurried and incomplete postings on the subject, the question is about parameter binding and sql injection, I recognise that this is slightly (read totally) off the former but entirely on the latter.
The method and the demonstration code I posted is a mean of evaluating the output of a dynamic sql without executing the code. To fortify it, one should split the input on any known batch delimiters or comment character combinations, only executing the first part if the code passes the test.
The methodology is simple, the structure of the result set should match the output of the query, if it doesn't, don't execute the query.
To throw off any attempts, return an empty result set with the expected structure. Most injection attacks work on the basis of reading and learning from results and error messages. By consistently returning the same result set structure, the attacker cannot derive anything (and quickly gets bored).
The sp_describe_first_result_set procedure is also very useful in ETL and can replace a rather long winded on contorted code in SSIS, but that's a story for later.
😎
May 22, 2014 at 1:30 pm
Eirikur Eiriksson (5/22/2014)
First of all, sorry for my hurried and incomplete postings on the subject, the question is about parameter binding and sql injection, I recognise that this is slightly (read totally) off the former but entirely on the latter.The method and the demonstration code I posted is a mean of evaluating the output of a dynamic sql without executing the code. To fortify it, one should split the input on any known batch delimiters or comment character combinations, only executing the first part if the code passes the test.
The methodology is simple, the structure of the result set should match the output of the query, if it doesn't, don't execute the query.
To throw off any attempts, return an empty result set with the expected structure. Most injection attacks work on the basis of reading and learning from results and error messages. By consistently returning the same result set structure, the attacker cannot derive anything (and quickly gets bored).
The sp_describe_first_result_set procedure is also very useful in ETL and can replace a rather long winded on contorted code in SSIS, but that's a story for later.
😎
OK Now I get what you were saying. I just missed it because the portion of what you posted had no context to the whole dynamic sql issue. It was zoomed in so far that I couldn't see how it related to this. Your explanation here helped me get it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply