Yu Tant

Home
delphi

Two techniques to executing a dynamic SQL statement

There are two techniques to executing a dynamic SQL statement. But I'll use a shorter SQL, to concentrate on the logic:

The pure way (using parameters)

    q.SQL.Text:=
        'INSERT INTO Each_Stock_Owned (Stock_Code, Stock_Name) '+
        'VALUES (:Stock_Code, :Stock_Name)';
    q.Prepare; //Optional
    q.ParamsByName('Stock_Code').AsString := Stock_Code;
    q.ParamsByName('Stock_Name').AsString := Stock_Name;
    q.ExecSQL;

The dirty way (building SQL)

    q.SQL.Text:=
        'INSERT INTO Each_Stock_Owned (Stock_Code, Stock_Name) VALUES ('+
    QuotedStr(Stock_Code) + ', '+
    QuotedStr(Stock_Name) + ')';
    q.ExecSQL;

The differences are significant. The dirty way exposes you to SQL injection problems (as in most other languages, when you build SQL dinamically but without parameters). This could be or not be a problem for you. If you know that the procedure is only called privately by your own code, and that those procedure parameter values can only contain good values... or if you do some good parameter checking before building and executing your SQL... then you are safe.


But if you do it with parameters (the pure way) you are automatically protected from SQL injection, as the SQL statement is validated by the engine, without knowing the parameter values. So the SQL statement structure is known by the engine and cannot be altered by the actual values.


Another consideration is how frequently you will execute that INSERT statement. The pure way allows you to prepare the query ONCE, and execute it MANY TIMES with different parameter values (you must not destroy the query object, nor change the SQL property, and you must call the Prepare method once). If you run it frequently within a loop then it can be more efficient than building the SQL many times the dirty way. OTOH if you just need to insert one single row, it may pose a bit more overhead.


=================