[freetds] a question about transaction

ZIGLIO, Frediano, VF-IT Frediano.Ziglio at vodafone.com
Mon Feb 13 08:01:39 EST 2006


> 
> hi:
> 
>    i am writing a programme which execute a transaction by 
> freetds ,  however  , there is a strange instance that no  
> code is returned ,but the sqlserver data had been influenced  
> .   i need the returnd code to execute the next step.  but nothing.
> 
> 
> i use the function run_command which is  programmed by myself 
> to execute sql sentence which have no returned result. 
> 
> 
> function:
> 
> 
> /* Run commands from which we expect no results returned */ 
> // Update,insert,delete
> static inline CS_RETCODE run_command(CS_COMMAND * cmd, const 
> char *sql )
> {
>  CS_RETCODE ret, results_ret;
>  CS_INT result_type;
>  
>  if (cmd == NULL) return CS_FAIL;
>  if (ct_command(cmd, CS_LANG_CMD, sql, CS_NULLTERM, 
> CS_UNUSED) != CS_SUCCEED) return CS_FAIL;
>  if (ct_send(cmd) != CS_SUCCEED) return CS_FAIL;
> 
>  while ( (results_ret = ct_results( cmd , &result_type ) ) == 
> CS_SUCCEED )
>  {
>   switch ((int) result_type) 
>   {
>   case CS_CMD_SUCCEED:
>    break;
>   case CS_CMD_DONE:
>    break;
>   case CS_CMD_FAIL:
>    return E_NORMAL;
>   default:
>    return CS_FAIL;
>   }
>  } 
>  switch ( (int) results_ret ) 
>  {
>  case CS_END_RESULTS:
>   break;
>  case CS_FAIL:
>  default:
>   return CS_FAIL;
>  }
> 
>  return CS_SUCCEED;
> }
> 

I'm not a CTLib expert so I can't help here

> 
> the transaction is :
> snprintf( szInsert,
>   SQL_BUFFER_LENGTH,
>   "BEGIN TRANSACTION addnewcarduser "
>   "Insert into authinfo2( uid,password) values( \'%s\',\'%s\')"
>   "Insert into secuinfo( uid,password2) values( \'%s\',\'%s\')"
>   "insert into statinfo(uid,reg_date,reg_ip) values( 
> \'%s\',\'%s\' ,%d)"
>   "insert into last_login(uid,last_login_time,last_login_ip) 
> values( \'%s\',\'%s\',%d)"
>   "insert into userinfo(uid) values( \'%s\')"
>   "insert into money(uid) values( \'%s\')"
>   "insert into point(uid) values( \'%s\')"
>   "update secuinfo set user_type=user_type+power(2,2) 
> ,service=service+power(2,3) where uid = \'%s\'" 
>         "insert into 
> op_log(uid,op_type,op_time,op_detail,op_ip) values( 
> \'%s\',11,\'%s\','新手卡激活:成功',%d)"
>   "COMMIT TRANSACTION addnewcarduser",
>        val->username ,
>   val->password ,
>         val->username ,
>   val->password ,
>   val->username ,
>   ChangeTmFormat(val->currentdate,szTime) ,
>   val->userip ,
>   val->username ,
>   ChangeTmFormat(val->currentdate,szTime) ,
>   val->userip ,
>         val->username ,
>         val->username ,
>   val->username ,
>         val->username ,
>   val->username ,
>   ChangeTmFormat(val->currentdate,szTime) ,
>   val->userip );
> 

Here I see some problems:
- sql injection (%s is not suffucient, string should be quoted)
- error handling, usually I write code like
  BEGIN TRANSACTION
  insert 1 ...
  IF @@ERROR <> 0
    insert 2 ...
  .. and so on ..
  IF @@ERROR <> 0 COMMIT ELSE ROLLBACK

now, which error aren't you able to catch?

freddy77


More information about the FreeTDS mailing list