Creating Safe SQL Statements as Strings

Creating safe SQL statements as strings

Use parameterised commands. Pass the parameters along to your remote server as well, and get that to call into SQL Server, still maintaining the distinction between the SQL itself and the parameter values.

As long as you never mix treat data as code, you should be okay.

How do I convert a string into safe SQL String?

Don't sanitize your strings. Use parameterized queries instead, as they handle all sanitization.

You don't specify which database you are using, so I assume it is MS SQL Server. Microsoft has an article on the official ASP.net website about this. Also see MSDN for SqlCommand.Parameters and the AddWithValue method.

Query strings safe or not?

A sql injection usually comes from bugs in code that runs server side and submit sql queries to a database. Many bugs in the way you implement this can result to a sql injection. You can read values from a url, but before you plug these values to a sql query you should make some checking.

In order to answer to your question, query strings are safe the way you use the variables that are in them may be not.

As for making your site not vulnerable to them you should implement all your data access layer code (calling of stored procedures, of CRUD operations, of functions etc.) not vulnerable to them. For instance if you use queries, in which you pass parameterized variables then you can avoid a great deal of sql injections. Please take a look here

https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

Avoid string concatenation to create queries

While there might be usecases where you build a prepared statement by string-concatenation before compiling it, it is always bad practice to insert query-parameters using string-concatenation for two reasons:

Performance: When using a prepared statement the query-syntax has to be parsed only once and the access-path has to be calculated only once for each distinct query-type. When building statements by string-concatenation parsing and optimizing has to be done for each execution of the query.
Security: Using string-concatenation with data provided by the user is always prone to SQL-injection-attacks. Suppose you got a statement:
```
query = "select secret_data from users where userid = '" + userid_param + "'";  
```

And imagine someone sends a userid_param containing "' OR 1=1;"...

This way the only way to defend is doing 100% correct input-sanitation which might be quite hard to get right depending on the language used. When using prepared statements with a properly implemented driver the driver will isolate the statement form the query-parameters so nothing will be mixed up.

Creating Safe SQL Statements as Strings