inserting input type data but gives error with sql

Question

My SQL Insert Query failed, this was the insert statement:

    INSERT INTO 0_sales_orders 
    ( order_no
    , type
    , debtor_no
    , trans_type
    , branch_code
    , customer_ref
    , reference
    , comments
    , ord_date
    , order_type
    , ship_via
    , deliver_to
    , delivery_address
    , contact_phone
    , freight_cost
    , from_stk_loc
    , delivery_date
    , payment_terms
    , total
    , prep_amount
    , ship_from
    ) VALUES 
    ('2'
    ,'0'
    ,'1'
    , '30'
    ,'2'
    ,''
    ,'002/2019'
    ,''
    ,'2018-12-31'
    ,'1'
    ,'1'
    ,'A'
    ,'Makati'
    ,'9224622988'
    ,'0'
    ,'DEF'
    ,'2019-01-01'
    ,'4'
    ,'0')

inserting ship_form in my database give error i don't know why?

My code for adding to database:

    $sql = "
    INSERT INTO ".TB_PREF."sales_orders 
    (order_no
    , type
    , debtor_no
    , trans_type
    , branch_code
    , customer_ref
    , reference
    , comments
    , ord_date
    , order_type
    , ship_via
    , deliver_to
    , delivery_address
    , contact_phone
    , freight_cost
    , from_stk_loc
    , delivery_date
    , payment_terms
    , total
    , prep_amount
    , ship_from
    ) VALUES 
    (" .db_escape($order_no) . "
    ," .db_escape($order_type) . "
    ," .db_escape($order->customer_id) ."
    ," .db_escape($order->trans_type) . "
    ," .db_escape($order->Branch) . "
    ,".db_escape($order->cust_ref) ."
    ,".db_escape($order->reference) ."
    ,".db_escape($order->Comments) ."
    ,'" .date2sql($order->document_date) . "'
    , " .db_escape($order->sales_type) . "
    , " .db_escape($order->ship_via)."
    ," .db_escape($order->deliver_to) . "
    ," .db_escape($order->delivery_address) . "
    , " .db_escape($order->phone) . "
    , " .db_escape($order->freight_cost) ."
    , " . db_escape($order->Location) ."
    , " .db_escape($del_date) . "
    ," .db_escape($order->payment) . "
    ," .db_escape($total). 
    ")"
    ;

    db_escape($ship_from). ")";
    db_escape($order->prep_amount).")";

Hint: stop what you are doing, remove `db_escape()` and `date2sql()` functions from your code totally and use [prepared statements](https://stackoverflow.com/questions/8263371/how-can-prepared-statements-protect-from-sql-injection-attacks).. it will solve two things the correct "escape" and protects fully against SQL injections, i doubt your custom function fully protects against SQl injections. — Raymond Nijland, Feb 26 '19 at 14:24
I agree with @Raymond Nijland . And once you have done that, you will realize that you use 21 column names in the insert statement and only provide 19 values. — nacho, Feb 26 '19 at 14:41

score -2 · Answer 1 · answered Feb 26 '19 at 14:21

-2

This is not sql problem...

Please review the way you are generating the sql query...

After db_escape($total) you are closing bracket ) and adding ; to the end of line, so ship_from and prep_amount not exist in $sql variable... also when you fix that check order of ship_from and prep_amount because upper you defined it in different order (ship_from needs to be before prep_amount)

answered Feb 26 '19 at 14:21

Sinisa Bobic

1,311
10
15

2

Don't suggest a unsafe way to work with a database, string concatenation in application code to generate SQL code is generally a very bad idea and considerd bad practice.. – Raymond Nijland Feb 26 '19 at 14:22
@RaymondNijland Agree, but right now just helping this guy to make code working and also helping him to understand error... We are too far away from making this code looking good... And still didn't want to give him working example, he must do it himself ;) – Sinisa Bobic Feb 26 '19 at 14:25
2

Right don't take that unknowing role in the internet curse where bad tutorials and unsafe code stays alive for a long time and crackers, hackers and scriptkiddies keep smiling when reading the code..Keep actively warn others about unsafe code which we do alot around here.. – Raymond Nijland Feb 26 '19 at 14:29

inserting input type data but gives error with sql

1 Answers1