Security Best Practices

Security Best Practices

Security should always be a primary consideration when building APIs that interact with databases. The Generic SQL API Framework is designed to encourage secure development practices, but developers are responsible for deploying and configuring applications securely.

---

Use Parameterized Queries

Always use parameters instead of concatenating user input.

✅ Recommended

SELECT *
FROM CustomerTable
WHERE CustomerID = :CustomerID;

❌ Avoid

SELECT *
FROM CustomerTable
WHERE CustomerID = '" + id + "';

Parameterized queries help protect against SQL injection attacks.

---

Validate Input

Validate incoming request data before executing any query.

Examples:

• Required fields
• Data types
• Maximum length
• Allowed values
• Date formats

Never assume client input is valid.

---

Restrict SQL Files

Only execute SQL files located inside the configured query directory.

Example:

queries/

Do not allow users to execute arbitrary SQL or specify file paths outside this directory.

---

Principle of Least Privilege

Create a dedicated SQL Server account for the framework.

Grant only the permissions required by your application.

Example:

• SELECT
• INSERT
• UPDATE
• EXECUTE (if required)

Avoid using:

sa

or other administrative accounts in production.

---

Protect Database Credentials

Store database credentials securely.

Do not:

• Commit passwords to Git
• Hardcode credentials in source code
• Share credentials publicly

Instead:

• Use environment variables
• Use secure configuration files
• Restrict file permissions

---

Secure API Access

If your application is publicly accessible, implement authentication.

Examples:

• API Keys
• JWT Tokens
• OAuth 2.0
• Session Authentication

Protect sensitive endpoints from unauthorized access.

---

Use HTTPS

Always deploy production applications over HTTPS.

Benefits:

• Encrypts data in transit
• Prevents packet sniffing
• Protects authentication tokens
• Increases user trust

Avoid transmitting credentials over plain HTTP.

---

Limit Database Exposure

Do not expose your SQL Server directly to the internet.

Recommended architecture:

Client
    │
HTTPS
    │
Web Server
    │
Generic SQL API Framework
    │
Internal Network
    │
SQL Server

Restrict database access using firewalls and network rules.

---

Error Handling

Do not expose internal database errors to clients.

❌ Avoid

{
    "message": "Invalid object name CustomerTable."
}

✅ Recommended

{
    "success": false,
    "message": "An unexpected error occurred."
}

Log detailed errors internally for troubleshooting.

---

Keep Software Updated

Regularly update:

• PHP
• SQL Server
• ODBC Driver
• Web Server
• Operating System
• Framework dependencies

Security updates help protect against known vulnerabilities.

---

Audit and Logging

Maintain logs for:

• API requests
• Validation failures
• Database errors
• Authentication events
• Unexpected exceptions

Review logs regularly to identify unusual activity.

---

Security Checklist

Before deploying to production:

• Parameterized queries are used.
• Input validation is enabled.
• HTTPS is configured.
• Database credentials are secured.
• SQL Server is not publicly accessible.
• Administrative accounts are not used.
• Error messages are sanitized.
• Logging is enabled.
• Software is fully updated.

---

Best Practices

• Follow the principle of least privilege.
• Never trust client input.
• Protect sensitive configuration files.
• Review logs regularly.
• Test security as part of your deployment process.

---

Next Steps

Continue with:

• Framework Architecture
• Contributing
• Frequently Asked Questions
• Troubleshooting