jeremylongshore/tons-of-skills-marketplace / plugins/database/stored-procedure-generator/skills/generating-stored-procedures
generating-stored-procedures
VerifiedUse when you need to generate, validate, or deploy stored procedures for PostgreSQL, MySQL, or SQL Server. Creates database functions, triggers, and procedures with proper error handling and transaction management. Trigger with phrases like "generate stored procedure", "create database function", "write SQL procedure", "add trigger to table", or "create CRUD procedures".
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 7.1 KB |
| assets/README.md | markdown | 383 B |
| config/settings.yaml | config | 1.7 KB |
| references/README.md | markdown | 624 B |
| references/database_security_guidelines.md | markdown | 5.9 KB |
| references/mysql_stored_procedure_best_practices.md | markdown | 7.0 KB |
| references/postgresql_stored_procedure_best_practices.md | markdown | 5.2 KB |
| references/sqlserver_stored_procedure_best_practices.md | markdown | 7.8 KB |
| references/stored_procedure_optimization_techniques.md | markdown | 6.6 KB |
| scripts/README.md | markdown | 514 B |
| scripts/database_connection_test.pyexecutable | script | 7.2 KB |
| scripts/stored_procedure_deployer.pyexecutable | script | 10.5 KB |
| scripts/stored_procedure_syntax_validator.pyexecutable | script | 10.3 KB |
references/database_security_guidelines.md
View on GitHub# Database Security Guidelines for Stored Procedures
## Principle of Least Privilege
Grant only the minimum permissions required:
```sql
-- PostgreSQL
GRANT EXECUTE ON FUNCTION get_user(INT) TO app_readonly;
REVOKE ALL ON FUNCTION admin_reset_password FROM PUBLIC;
-- MySQL
GRANT EXECUTE ON PROCEDURE mydb.GetUserById TO 'appuser'@'localhost';
-- SQL Server
GRANT EXECUTE ON dbo.GetUserById TO AppUser;
```
## SQL Injection Prevention
### Never Concatenate User Input
```sql
-- VULNERABLE
CREATE PROCEDURE bad_search(p_name VARCHAR(100))
AS $$
BEGIN
EXECUTE 'SELECT * FROM users WHERE name = ''' || p_name || '''';
END;
$$ LANGUAGE plpgsql;
-- Input: '; DROP TABLE users; --
-- Results in: SELECT * FROM users WHERE name = ''; DROP TABLE users; --'
```
### Use Parameterized Queries
```sql
-- PostgreSQL (Safe)
CREATE FUNCTION safe_search(p_name VARCHAR)
RETURNS SETOF users AS $$
BEGIN
RETURN QUERY SELECT * FROM users WHERE name = p_name;
END;
$$ LANGUAGE plpgsql;
-- MySQL (Safe with prepared statements)
DELIMITER //
CREATE PROCEDURE SafeSearch(IN p_name VARCHAR(100))
BEGIN
SET @sql = 'SELECT * FROM users WHERE name = ?';
SET @name = p_name;
PREPARE stmt FROM @sql;
EXECUTE stmt USING @name;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
-- SQL Server (Safe with sp_executesql)
CREATE PROCEDURE dbo.SafeSearch
@Name NVARCHAR(100)
AS
BEGIN
DECLARE @SQL NVARCHAR(200) = N'SELECT * FROM Users WHERE Name = @SearchName';
EXEC sp_executesql @SQL, N'@SearchName NVARCHAR(100)', @Name;
END;
```
## Input Validation
Validate all inputs before use:
```sql
-- PostgreSQL
CREATE FUNCTION validate_email(p_email VARCHAR)
RETURNS BOOLEAN AS $$
BEGIN
IF p_email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
RAISE EXCEPTION 'Invalid email format: %', p_email
USING ERRCODE = 'P0001';
END IF;
RETURN TRUE;
END;
$$ LANGUAGE plpgsql;
-- SQL Server
CREATE PROCEDURE dbo.ValidateAndInsertUser
@Email NVARCHAR(255),
@Age INT
AS
BEGIN
-- Validate email format
IF @Email NOT LIKE '%_@__%.__%'
THROW 50001, 'Invalid email format', 1;
-- Validate age range
IF @Age < 0 OR @Age > 150
THROW 50002, 'Age must be between 0 and 150', 1;
INSERT INTO Users (Email, Age) VALUES (@Email, @Age);
END;
```
## Sensitive Data Protection
### Avoid Logging Sensitive Data
```sql
-- BAD: Password in error message
RAISE NOTICE 'Login failed for password: %', p_password;
-- GOOD: Mask sensitive data
RAISE NOTICE 'Login failed for user: %', p_username;
```
### Use Encryption for Sensitive Columns
```sql
-- PostgreSQL with pgcrypto
CREATE FUNCTION store_secret(p_user_id INT, p_secret TEXT, p_key TEXT)
RETURNS VOID AS $$
BEGIN
UPDATE users
SET encrypted_secret = pgp_sym_encrypt(p_secret, p_key)
WHERE id = p_user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
```
## SECURITY DEFINER vs SECURITY INVOKER
### SECURITY DEFINER (PostgreSQL)
Executes with function owner's privileges:
```sql
-- Allows controlled access to sensitive table
CREATE FUNCTION admin_get_user_count()
RETURNS BIGINT
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
SELECT COUNT(*) FROM admin.sensitive_users;
$$ LANGUAGE sql;
-- Regular users can call this but not access admin.sensitive_users directly
GRANT EXECUTE ON FUNCTION admin_get_user_count() TO app_user;
```
### SQL SECURITY DEFINER (MySQL)
```sql
CREATE DEFINER='admin'@'localhost' PROCEDURE GetSensitiveData()
SQL SECURITY DEFINER
BEGIN
SELECT * FROM sensitive_table;
END;
```
### EXECUTE AS (SQL Server)
```sql
CREATE PROCEDURE dbo.GetSensitiveData
WITH EXECUTE AS 'AdminUser'
AS
BEGIN
SELECT * FROM dbo.SensitiveTable;
END;
```
## Audit Logging
Track who does what:
```sql
-- PostgreSQL audit trigger
CREATE TABLE audit_log (
id SERIAL PRIMARY KEY,
table_name VARCHAR(100),
operation VARCHAR(10),
old_data JSONB,
new_data JSONB,
changed_by VARCHAR(100) DEFAULT current_user,
changed_at TIMESTAMP DEFAULT NOW()
);
CREATE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO audit_log (table_name, operation, old_data, new_data)
VALUES (
TG_TABLE_NAME,
TG_OP,
CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD) ELSE NULL END,
CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN row_to_json(NEW) ELSE NULL END
);
RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
```
## Rate Limiting
Prevent abuse:
```sql
-- PostgreSQL rate limiting
CREATE FUNCTION check_rate_limit(p_user_id INT, p_action VARCHAR)
RETURNS BOOLEAN AS $$
DECLARE
v_count INT;
BEGIN
SELECT COUNT(*) INTO v_count
FROM rate_limit_log
WHERE user_id = p_user_id
AND action = p_action
AND created_at > NOW() - INTERVAL '1 minute';
IF v_count >= 100 THEN
RAISE EXCEPTION 'Rate limit exceeded for action: %', p_action
USING ERRCODE = 'P0429';
END IF;
INSERT INTO rate_limit_log (user_id, action) VALUES (p_user_id, p_action);
RETURN TRUE;
END;
$$ LANGUAGE plpgsql;
```
## Connection Security
### Use SSL/TLS
```sql
-- PostgreSQL: Check SSL status
SELECT ssl, version FROM pg_stat_ssl WHERE pid = pg_backend_pid();
-- MySQL: Require SSL for user
CREATE USER 'secure_user'@'%' REQUIRE SSL;
```
### Limit Connection Sources
```sql
-- PostgreSQL: pg_hba.conf restricts connections
-- MySQL: User@Host pattern
CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'password';
```
## Security Checklist
| Category | Check | Status |
|----------|-------|--------|
| Authentication | Procedures don't bypass auth | Required |
| Authorization | Least privilege enforced | Required |
| Input Validation | All inputs sanitized | Required |
| SQL Injection | No string concatenation | Required |
| Error Handling | No sensitive data in errors | Required |
| Logging | Audit trail enabled | Recommended |
| Encryption | Sensitive data encrypted | Recommended |
| Rate Limiting | Abuse prevention in place | Recommended |
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| writing-skillsobra/superpowersUse when creating new skills, editing existing skills, or verifying skills work before deployment | Stars: 323k | Updated | Claude CodeCodex | Verified | Install |
| xlsxanthropics/skillsUse this skill any time a spreadsheet file is the primary input or output. This means any task where the user wants to: open, read, edit, or fix an existing .xlsx, .xlsm, .xltx, .csv, or .tsv file (e.g., adding columns, computing formulas, formatting, charting, cleaning messy data); create a new spreadsheet from scratch or from other data sources; or convert between tabular file formats. Trigger especially when the user references a spreadsheet file by name or path — even casually (like "the xlsx in my downloads") — and wants something done to it or produced from it. Also trigger for cleaning or restructuring messy tabular data files (malformed rows, misplaced headers, junk data) into proper spreadsheets. The deliverable must be a spreadsheet file. Do NOT trigger when the primary deliverable is a Word document, HTML report, standalone Python script, database pipeline, or Google Sheets API integration, even if tabular data is involved. | Stars: 198k | Updated | Claude CodeCodex | Verified | Install |
| shipping-and-launchaddyosmani/agent-skillsPrepares production launches. Use when preparing to deploy to production, or when asking what needs to be in place before shipping. Use when you need a pre-launch checklist, when setting up monitoring, when planning a staged rollout, or when you need a rollback strategy. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| childhoodthedotmack/claude-memBabysit a shipped release after merge, publication, or deployment. Verify published packages, production behavior, installed plugins, and late CI or user reports until the observation window is clean; use babysit for PR readiness before release. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |