如何通过向WHERE子句传递变量查询数据库字段及代码咨询

Hey there! Let's go through your code and tackle your database question together. First, I'll fix the syntax/security issues in your existing functions, then break down how to safely pass variables to a SQL WHERE clause.

I spotted a few small bugs and security risks in your code—here's the cleaned-up version:

Corrected process_newreceipt() #

public function process_newreceipt() {
    $Bank = $this->test_data->test_input($_POST["Bank"]);
    $Member = $this->test_data->test_input($_POST["Member"]);
    $Amount = $this->test_data->test_input($_POST["Amount"]);
    
    $phone = $this->MobileNo($Member);
    $message = 'Thanks';
    
    // Fixed unclosed parentheses in the new_receipt call
    $insert = $this->receipt_model->new_receipt($Bank, $Member, $Amount);
    
    if ($insert >= 1) {
        // Add a check for valid phone number before sending
        if (!empty($phone)) {
            sendsms($phone, $message);
        }
        // Flash messages work better than alert + redirect (alert might be skipped)
        $this->session->set_flashdata('success', 'Transaction recorded successfully!');
        redirect(base_url('index.php/Receipt/Receipt_Transactions'));
    }
}

Corrected MobileNo() (with Security Fixes) #

public function MobileNo($member) {
    // Fixed variable case mismatch ($Query vs $query)
    // Used CodeIgniter's query binding to avoid SQL injection
    $query = $this->db->query("SELECT MOBILE FROM MEMBERS WHERE ID_NO = ?", [$member]);
    
    // Check if a result exists to avoid errors
    if ($query->num_rows() > 0) {
        $row = $query->row();
        // Match the case of your database column (MOBILE vs mobile)
        return $row->MOBILE;
    } else {
        // Return null if no member found, adjust as needed
        return null;
    }
}

Directly inserting variables into your SQL string (like your original MobileNo function) is dangerous—it leaves you open to SQL injection attacks. Here are the safest ways to do this in CodeIgniter:

Method 1: Query Binding (Recommended) #

Use ? as a placeholder for your variable, then pass the variable in an array as the second argument. CodeIgniter automatically escapes the value to prevent injection:

// Single variable
$query = $this->db->query("SELECT NAME FROM MEMBERS WHERE ID_NO = ?", [$member_id]);

// Multiple variables
$query = $this->db->query(
    "SELECT * FROM MEMBERS WHERE ID_NO = ? AND STATUS = ?",
    [$member_id, 'active']
);

Method 2: Query Builder (Active Record) #

This is a more readable, fluent way to build queries—CodeIgniter handles escaping automatically:

public function getMemberDetails($member_id) {
    $query = $this->db->select('MOBILE, NAME')
                      ->from('MEMBERS')
                      ->where('ID_NO', $member_id)
                      ->get();
    
    return $query->row();
}

Method 3: Manual Escaping (Not Recommended) #

If you absolutely need to concatenate variables, use $this->db->escape() to sanitize them:

$escaped_id = $this->db->escape($member_id);
$query = $this->db->query("SELECT MOBILE FROM MEMBERS WHERE ID_NO = $escaped_id");

Stick to the first two methods whenever possible—they're less error-prone.

• Make sure your new_receipt model method also uses one of these safe database practices to avoid injection.
• Always validate user input (you're already using test_input, which is great!).
• Flash messages (like the one added to process_newreceipt) are more reliable than JavaScript alerts for post-redirect feedback.

内容的提问来源于stack exchange，提问作者shweiz jemo