Ruby on Rails development from scratch (46)-ActiveRecord Basics (SQL and active record)

Imagine how an active record handles SQL, so let's take a look at the Find method's: Conditions parameter, which is like this when called: Find (: all,:conditions=> ...), here: The conditions parameter determines which records the Find method returns, which corresponds to the where part of the SQL statement, for example, to get all the orders named Dave,pay_type Po, which we write:

pos = Order.find (: all,:conditions => "name = ' Dave ' and pay_type = ' po '")

The Find method returns all eligible records and has been converted to an object of the order class, and if there are no orders that match the criteria, the Find method returns an array of zero length.

If your condition is predetermined, then the above wording is good, but if we want to specify the value of the condition, we write this:

# Get the limit amount from the form
name = Params[:name]
# DON ' T does this!!!
pos = Order.find (: all,:conditions => "name = ' #{name} ' and Pay_type = ' po '")

But that's not a good idea, because if your program is going to be posted on the web, which is handy for SQL injection attacks (we'll talk about SQL injection in the topic of security), a better approach would be to dynamically generate SQL (note 1) and let the active record handle it , we can add placeholders in the SQL statements, and these placeholders will be replaced at run time with the specified values, and the method of specifying placeholders is to use the question mark in SQL, at run time,

The first question mark is replaced with the value of the second element of the collection, and so on, for example, we rewrite the above query once:
name = params[:name]
pos = order.find (: all,:conditions => [" Name =? and Pay_type = ' po ', name]

We can also use a placeholder with a name, preceded by a colon, such as the following:

Name = Params[:name]
pay_type = params[:p Ay_type]
pos = Order.find (: All,
: Conditions => ["name =: Name and Pay_type =:p Ay_type ",
{:p ay_type => pay_type,: Name => name}])

I can also go further because params is a hash and we can make the conditions part simpler

pos = Order.find (: All,
: Conditions => ["name =: Name and Pay_type =:p Ay_type", params])

Regardless of which placeholder you use, the Active record handles quotes and escape in SQL very carefully. Using the dynamic sql,active record protects us from SQL injection attacks.

NOTE 1: Dynamic SQL here is different from the dynamic SQL referred to in databases such as Oracle, which is similar to the ado.net of not stitching SQL strings, but passing parameters to prevent SQL injection attacks.