How to Re-Write a SQL Query as a Parameterized Query

how to change sql statement to parameterized query?

I have the answer. c.CurrencyCode = '" + Code.Replace("'", "''") + "' simply changes to c.CurrencyCode = ?code

How do I create a parameterized SQL query? Why Should I?

The EXEC example in the question would NOT be parameterized. You need parameterized queries (prepared statements in some circles) to prevent input like this from causing damage:

';DROP TABLE bar;--

Try putting that in your fuz variable (or don't, if you value the bar table). More subtle and damaging queries are possible as well.

Here's an example of how you do parameters with Sql Server:

Public Function GetBarFooByBaz(ByVal Baz As String) As String
    Dim sql As String = "SELECT foo FROM bar WHERE baz= @Baz"

    Using cn As New SqlConnection("Your connection string here"), _
        cmd As New SqlCommand(sql, cn)

        cmd.Parameters.Add("@Baz", SqlDbType.VarChar, 50).Value = Baz
        Return cmd.ExecuteScalar().ToString()
    End Using
End Function

Stored procedures are sometimes credited with preventing SQL injection. However, most of the time you still have to call them using query parameters or they don't help. If you use stored procedures exclusively, then you can turn off permissions for SELECT, UPDATE, ALTER, CREATE, DELETE, etc (just about everything but EXEC) for the application user account and get some protection that way.

How to rewrite SQL query without IN clause

It isn't clear to me why you are getting ORA-01795. Your subquery only selects a max value, which should be a single value. In addition, the 1000 value limit only applies to a list of literals, not a subquery. In any case, you could rephrase this query using a join instead of WHERE IN:

SELECT 
    b.MESSAGE,
    b.ITEMID
FROM a
INNER JOIN b
    ON b.aid = a.aid AND b.ITEMTYPE = 'XYZ'
INNER JOIN
(
    SELECT
        b1.itemid,
        MAX(receive_date) AS max_receive_date
    FROM a a1
    INNER JOIN b b1
        ON b1.aid = a1.aid   
    GROUP BY b1.itemid
) t
    ON b.itemid = t.itemid
WHERE a.receive_date = t.max_receive_date

Rewriting dynamic SQL in to stored procedure

your stored procedure will have 3 parameters @param_1, @param_2, @param_3

Default value for these parameters can be set to NULL.

The condition inside the stored procedure will be like this

WHERE  ( @param_1 is NULL or field_1 = @param_1)
       AND 
       ( @param_2 is NULL or field_2 = @param_2)
       AND
       ( @param_3 is NULL or field_3 = @param_3)

OPTOIN(RECOMPILE)

Adding OPTION(RECOMPILE) rebuilds the execution plan every time that your query executes, with OR conditions this would be beneficial.

Rewrite a T-SQL query containing subqueries to using joins

So something like this:

SELECT s.*
FROM DynamicField t
INNER JOIN DynamicField t1 on(t.parentID = t1.DynamicFieldID)
INNER JOIN Application s ON(t1.parentID = s.DynamicFieldID)

This will select all from Application table.

Rewrite the query to convert multiple select statements into one select

You could form an inline table of all regions, and then use a single one shot query:

BEGIN
    SELECT o.orderId, o.ordername, c.custName, c.custPhone
    FROM dbo.customer c
    INNER JOIN dbo.order o ON c.custId = o.custId
    INNER JOIN dbo.region r ON o.regionId = r.regionId
    INNER JOIN
    (
        SELECT regionId, 'North' AS region FROM dbo.regionNorth UNION ALL
        SELECT regionId, 'South' FROM dbo.regionSouth           UNION ALL
        SELECT regionId, 'East' FROM dbo.regionEast             UNION ALL
        SELECT regionId, 'West' FROM dbo.regionWest
    ) rn
        ON rn.regionId = r.regionId
    WHERE
        c.custId = @custId AND
        r.regionId = @regionId AND
        rn.region = @regionId
END

What is parameterized query?

A parameterized query (also known as a prepared statement) is a means of pre-compiling a SQL statement so that all you need to supply are the "parameters" (think "variables") that need to be inserted into the statement for it to be executed. It's commonly used as a means of preventing SQL injection attacks.

You can read more about these on PHP's PDO page (PDO being a database abstraction layer), although you can also make use of them if you're using the mysqli database interface (see the prepare documentation).

---

Related Topics