NCBI C++ ToolKit
sdbapi_simple.cpp
Go to the documentation of this file.

Go to the SVN repository for this file.

1 /* $Id: sdbapi_simple.cpp 71417 2016-03-03 16:46:54Z gouriano $
2  * ===========================================================================
3  *
4  * PUBLIC DOMAIN NOTICE
5  * National Center for Biotechnology Information
6  *
7  * This software/database is a "United States Government Work" under the
8  * terms of the United States Copyright Act. It was written as part of
9  * the author's official duties as a United States Government employee and
10  * thus cannot be copyrighted. This software/database is freely available
11  * to the public for use. The National Library of Medicine and the U.S.
12  * Government have not placed any restriction on its use or reproduction.
13  *
14  * Although all reasonable efforts have been taken to ensure the accuracy
15  * and reliability of the software and data, the NLM and the U.S.
16  * Government do not and cannot warrant the performance or results that
17  * may be obtained by using this software or data. The NLM and the U.S.
18  * Government disclaim all warranties, express or implied, including
19  * warranties of performance, merchantability or fitness for any particular
20  * purpose.
21  *
22  * Please cite the author in any work or product based on this material.
23  *
24  * ===========================================================================
25  *
26  * Authors: David McElhany
27  *
28  * File Description:
29  *
30  * This program demonstrates connecting to a database and performing several
31  * simple operations, in order of preference:
32  * - executing a stored procedure
33  * - executing a static SQL statement
34  * - executing a parameterized SQL statement
35  * - executing a dynamic SQL statement
36  *
37  * Here's how to choose which form of SQL statement creation to use:
38  * 1. If stored procuedures can be used, then use them. This increases both
39  * security and performance. Plus, this practice could facilitate testing
40  * and documentation.
41  * 2. Otherwise, if the SQL statement does not require construction, then use
42  * static SQL.
43  * 3. Otherwise, if parameterized SQL can be used, then use it.
44  * 4. Otherwise, as a last resort, use dynamic SQL. NOTE: If user-supplied
45  * data is used to construct the statement, then you MUST sanitize the
46  * user-supplied data.
47  *
48  * For educational purposes, this program also shows how constructing SQL
49  * statements from unsanitized user input can result in SQL injection - and
50  * therefore why user input must always be sanitized when using dynamic SQL.
51  *
52  * The program is written assuming static linkage and a connection to
53  * SQL Server.
54  */
55 
56 #include <ncbi_pch.hpp>
57 
58 #include <corelib/ncbiapp.hpp>
59 #include <corelib/ncbiargs.hpp>
60 
61 #include <dbapi/simple/sdbapi.hpp>
62 
63 
65 
66 
67 /////////////////////////////////////////////////////////////////////////////
68 // CSdbapiSimpleApp::
69 
71 {
72 private:
73  virtual void Init(void);
74  virtual int Run(void);
75 
76  void ParseArgs(void);
77  void SetupDb(void);
78 
79  void DemoStoredProc(void);
80  void DemoParamerizedSql(void);
81  void DemoStaticSql(void);
82  void DemoDynamicSql(void);
83 
84  void DemoSqlInjection(void);
85 
86  // Command-line options
87  string m_UserString1;
88  string m_UserString2;
89  string m_UserString3;
90  string m_Service;
91  string m_DbName;
92  string m_User;
93  string m_PwFile;
94 
95  // Application data
97 };
98 
99 
100 /////////////////////////////////////////////////////////////////////////////
101 // Init - setup command-line arguments
102 
104 {
105  CArgDescriptions* argdesc = new CArgDescriptions();
106  argdesc->SetUsageContext(GetArguments().GetProgramBasename(),
107  "SDBAPI simple operations demo");
108 
109 
110  argdesc->AddDefaultKey("user_string1", "UserString1", "A user-supplied "
111  "string to be used in one of the demonstrations "
112  "(could contain a SQL injection attempt)",
114 
115  argdesc->AddDefaultKey("user_string2", "UserString2", "Another "
116  "user-supplied string to be used in one of the "
117  "demonstrations (could contain a SQL injection "
118  "attempt)",
120 
121  argdesc->AddDefaultKey("user_string3", "UserString3", "Yet another "
122  "user-supplied string to be used in one of the "
123  "demonstrations (could contain a SQL injection "
124  "attempt)",
126 
127  argdesc->AddPositional("service", "Service name",
129 
130  argdesc->AddPositional("db_name", "Database name",
132 
133  argdesc->AddPositional("user", "Database user name",
135 
136  argdesc->AddPositional("pw_file", "File containing database user password",
138 
139 
140  SetupArgDescriptions(argdesc);
141 }
142 
143 
144 /////////////////////////////////////////////////////////////////////////////
145 // Run - i.e. parse command-line arguments and demo simple operations.
146 
147 
149 {
150  // Get user parameters and connect to database.
151  ParseArgs();
152  SetupDb();
153 
154  // Demo several simple operations - in order of preference.
155  DemoStoredProc();
156  DemoStaticSql();
158  DemoDynamicSql();
159 
160  // Show how SQL injection can happen.
162 
163  return 0;
164 }
165 
166 
167 /////////////////////////////////////////////////////////////////////////////
168 // ParseArgs - parse the command-line arguments.
169 
171 {
172  const CArgs& args = GetArgs();
173 
174  m_UserString1 = args["user_string1"].AsString();
175  m_UserString2 = args["user_string2"].AsString();
176  m_UserString3 = args["user_string3"].AsString();
177  m_Service = args["service"].AsString();
178  m_DbName = args["db_name"].AsString();
179  m_User = args["user"].AsString();
180  m_PwFile = args["pw_file"].AsString();
181 }
182 
183 
184 /////////////////////////////////////////////////////////////////////////////
185 // SetupDb - connect to the database.
186 
188 {
189  // Specify connection parameters.
190  // If you are inside NCBI and need to know what credentials to use,
191  // email cpp-core.
192  CSDB_ConnectionParam db_params;
193  db_params
198 
199  // Connect to the database.
200  m_Db = CDatabase(db_params);
201  m_Db.Connect();
202 }
203 
204 
205 /////////////////////////////////////////////////////////////////////////////
206 // DemoStoredProc - demonstrate executing a stored procedure.
207 //
208 // See the File Description comment to determine if a stored procedure
209 // should be used.
210 //
211 // This demo uses a stored procedure that:
212 // 1. Sets recommended settings.
213 // 2. Selects some data per parameters.
214 // 3. Assigns an output parameter.
215 //
216 // In case you don't have access to the stored procedure, please see the
217 // companion file sdbapi_simple_sproc.sql.
218 //
219 // NOTE: It is recommended that you create your stored procedures using the
220 // following settings because these settings are generally advisable
221 // (and they cannot be changed at run-time):
222 // SET ANSI_NULLS ON
223 // SET QUOTED_IDENTIFIER ON
224 //
225 // Also, executing stored procedures using the following settings is
226 // recommended unless there is a specific reason to use other settings:
227 // SET ANSI_PADDING ON
228 // SET ANSI_WARNINGS ON
229 // SET ARITHABORT ON
230 // SET CONCAT_NULL_YIELDS_NULL ON
231 // SET NUMERIC_ROUNDABORT OFF
232 
234 {
235  // Pick a stored procedure.
236  string proc_name("sdbapi_simple_sproc");
237 
238  // Create an integer input parameter "@max_id", a float input
239  // parameter "@max_fl", and an integer output parameter "@num_rows".
241  query.SetParameter("@max_id", 5);
242  query.SetParameter("@max_fl", 5.1f);
243  query.SetParameter("@num_rows", 0, eSDB_Int4, eSP_InOut);
244 
245  // Execute the stored procedure.
246  NcbiCout << "\nExecuting stored proc \"" << proc_name << "\":" << NcbiEndl;
247  query.ExecuteSP(proc_name);
248  // query.RequireRowCount(...);
249 
250  // Print the results.
251  //
252  // NOTE: For database APIs, array-like indexes are 1-based, not 0-based!
253  //
254  NcbiCout << "int_val fl_val" << NcbiEndl;
255  ITERATE(CQuery, row, query.SingleSet()) {
256  NcbiCout
257  << row[1].AsInt4() << " "
258  << row[2].AsFloat() << NcbiEndl;
259  }
260 
261  // Confirm having read all results.
262  query.VerifyDone();
263 
264  // Print the number of result rows.
265  NcbiCout
266  << "Number of rows: "
267  << query.GetParameter("@num_rows").AsInt4() << NcbiEndl;
268 }
269 
270 
271 /////////////////////////////////////////////////////////////////////////////
272 // DemoStaticSql - demonstrate executing static SQL.
273 //
274 // See the File Description comment to determine if static SQL
275 // should be used.
276 //
277 // While stored procedures are preferable to static SQL, there is no chance
278 // of SQL injection with static SQL. Certainly you can write destructive
279 // static SQL yourself (e.g. DROP my_table), but that wouldn't be
280 // SQL injection.
281 
283 {
284  // Create a static SQL statement.
285  string sql("SELECT [title] FROM [Journal]");
286 
287  // Execute the static SQL.
288  NcbiCout << "\nExecuting static SQL \"" << sql << "\":" << NcbiEndl;
289  CQuery query = m_Db.NewQuery(sql);
290  query.Execute();
291  // query.RequireRowCount(...);
292 
293  // Print the results.
294  //
295  // NOTE: For database APIs, array-like indexes are 1-based, not 0-based!
296  //
297  NcbiCout << "title" << NcbiEndl;
298  ITERATE(CQuery, row, query.SingleSet()) {
299  NcbiCout << row[1].AsString() << NcbiEndl;
300  }
301  query.VerifyDone();
302 }
303 
304 
305 /////////////////////////////////////////////////////////////////////////////
306 // DemoParamerizedSql - demonstrate executing parameterized SQL.
307 //
308 // See the File Description comment to determine if parameterized SQL
309 // should be used.
310 //
311 // This function allows users to enter _any_ string (possibly containing
312 // SQL injection attempts) but doesn't require sanitizing the string.
313 // That's because the string is passed as a parameter and SQL Server
314 // guarantees that it is not susceptible to SQL injection when parameters are
315 // used.
316 
318 {
319  // Get user-supplied strings.
320  const CArgs& args = GetArgs();
321  string user_last = args["user_string1"].AsString();
322  string user_salary = args["user_string2"].AsString();
323  string user_hire = args["user_string3"].AsString();
324 
325  // Create a parameterized SQL statement.
326  string sql(" SELECT [id], [last], [first], [salary], [hiredate]"
327  " FROM [Employee] WHERE [last] LIKE @last"
328  " AND [salary] > @salary"
329  " AND [hiredate] > @hire");
330 
331  // Assign parameters.
333  query.SetParameter("@last", user_last);
334  query.SetParameter("@salary", user_salary);
335  query.SetParameter("@hire", user_hire);
336 
337  // Execute the parameterized SQL.
338  NcbiCout << "\nExecuting parameterized SQL \"" << sql << "\":" << NcbiEndl;
339  query.SetSql(sql);
340  query.Execute();
341  // query.RequireRowCount(...);
342 
343  // Print the results.
344  //
345  // NOTE: For database APIs, array-like indexes are 1-based, not 0-based!
346  //
347  NcbiCout << "id last first salary hiredate" << NcbiEndl;
348  ITERATE(CQuery, row, query.SingleSet()) {
349  NcbiCout
350  << row[1].AsInt4() << " "
351  << row[2].AsString() << " "
352  << row[3].AsString() << " "
353  << row[4].AsInt4() << " "
354  << row[5].AsString() << NcbiEndl;
355  }
356  query.VerifyDone();
357 }
358 
359 
360 /////////////////////////////////////////////////////////////////////////////
361 // DemoDynamicSql - demonstrate executing dynamic SQL.
362 //
363 // See the File Description comment to determine if dynamic SQL
364 // should be used.
365 //
366 // If you work at NCBI and you believe your application requires dynamic SQL,
367 // please email cpp-core so that we'll be aware of any internal applications
368 // that require dynamic SQL.
369 //
370 // NOTE: If user-supplied data is used to construct the statement, then you
371 // MUST sanitize the user-supplied data, otherwise your code will be
372 // vulnerable to SQL injection attacks.
373 //
374 // If you are inside NCBI and using SQL Server or Sybase, sanitizing requires
375 // calling NStr::SQLEncode() for strings and round-tripping scalar types.
376 // For other database systems, contact cpp-core.
377 
379 {
380  // Get user-supplied strings.
381  const CArgs& args = GetArgs();
382  string user_last = args["user_string1"].AsString();
383  string user_salary = args["user_string2"].AsString();
384  string user_hire = args["user_string3"].AsString();
385 
386  // Sanitize.
387  // For strings, use SQLEncode(). For scalars, first convert to an
388  // appropriate scalar type, then convert back to a string.
389  user_last = NStr::SQLEncode(CUtf8::AsUTF8(user_last,eEncoding_ISO8859_1));
390  user_salary = NStr::UIntToString(NStr::StringToUInt(user_salary));
391  user_hire = "'" + CTime(user_hire).AsString() + "'";
392 
393  // Dynamically create a SQL statement.
394  string sql(" SELECT [id], [last], [first], [salary], [hiredate]"
395  " FROM [Employee] WHERE [last] LIKE " + user_last +
396  " AND [salary] > " + user_salary +
397  " AND [hiredate] > " + user_hire);
398 
399  // Execute the dynamic SQL.
400  NcbiCout << "\nExecuting dynamic SQL \"" << sql << "\":" << NcbiEndl;
402  query.SetSql(sql);
403  query.Execute();
404  // query.RequireRowCount(...);
405 
406  // Print the results.
407  //
408  // NOTE: For database APIs, array-like indexes are 1-based, not 0-based!
409  //
410  NcbiCout << "id last first salary hiredate" << NcbiEndl;
411  ITERATE(CQuery, row, query.SingleSet()) {
412  NcbiCout
413  << row[1].AsInt4() << " "
414  << row[2].AsString() << " "
415  << row[3].AsString() << " "
416  << row[4].AsInt4() << " "
417  << row[5].AsString() << NcbiEndl;
418  }
419  query.VerifyDone();
420 }
421 
422 
423 /////////////////////////////////////////////////////////////////////////////
424 // DemoSqlInjection - demonstrate how SQL injection can happen.
425 //
426 // This function is simply for educational purposes -- to show how code that
427 // constructs a SQL statement from unsanitized user input is vulnerable to
428 // SQL injection.
429 
431 {
432 #if 0
433  // Get user-supplied string.
434  const CArgs& args = GetArgs();
435  string user_input = args["user_string1"].AsString();
436 
437  // DO NOT DO THIS -- IT ALLOWS SQL INJECTION.
438  // Dynamically create a SQL statement directly from unsanitized user input.
439  string sql(" SELECT [id], [last], [first], [salary], [hiredate]"
440  " FROM [Employee] WHERE [last] LIKE '" + user_input + "'");
441 
442  // If you did the above, then the 'sql' string could contain malicious SQL.
443  // For example, the user input:
444  // a' OR 1=1; DROP TABLE [Employee]; --
445  // would result in the SQL statement:
446  // SELECT [id], [last], [first], [salary], [hiredate]
447  // FROM [Employee] WHERE [last] LIKE 'a' OR 1=1; DROP TABLE [emp]; --'
448 #endif
449 }
450 
451 
452 /////////////////////////////////////////////////////////////////////////////
453 // main
454 
455 int NcbiSys_main(int argc, ncbi::TXChar* argv[])
456 {
457  return CSdbapiSimpleApp().AppMain(argc, argv);
458 }
void Connect(void)
Explicitly (re)connect to the database server.
Definition: sdbapi.cpp:1864
CQuery NewQuery(void)
Get new CQuery object for this database.
Definition: sdbapi.cpp:1933
static string UIntToString(unsigned int value, TNumToStringFlags flags=0, int base=10)
Convert UInt to string.
Definition: ncbistr.hpp:5104
void SetParameter(CTempString name, const string &value, ESDB_Type type=eSDB_String, ESP_ParamType param_type=eSP_In)
Assign string value to the parameter.
Definition: sdbapi.cpp:3676
void DemoStaticSql(void)
void AddDefaultKey(const string &name, const string &synopsis, const string &comment, EType type, const string &default_value, TFlags flags=0, const string &env_var=kEmptyStr, const char *display_value=nullptr)
Add description for optional key with default value.
Definition: ncbiargs.cpp:2256
void Execute(const CTimeout &timeout=CTimeout(CTimeout::eDefault))
Explicitly execute sql statement.
Definition: sdbapi.cpp:3798
Defines command line argument related classes.
void DemoDynamicSql(void)
Note: From the point of view of the C++.
Definition: ncbistr.hpp:200
virtual void SetupArgDescriptions(CArgDescriptions *arg_desc)
Setup the command line argument descriptions.
Definition: ncbiapp.cpp:926
#define NcbiCout
Definition: ncbistre.hpp:398
void DemoStoredProc(void)
#define NcbiEndl
Definition: ncbistre.hpp:403
int AppMain(int argc, const char *const *argv, const char *const *envp=0, EAppDiagStream diag=eDS_Default, const char *conf=NcbiEmptyCStr, const string &name=NcbiEmptyString)
Main function (entry point) for the NCBI application.
Definition: ncbiapp.cpp:611
Int4 AsInt4(void) const
Get value as 4-byte integer.
Definition: sdbapi.cpp:3465
void DemoParamerizedSql(void)
Like Transact-SQL INT (signed 32-bit integer)
Definition: sdbapi.hpp:156
#define ITERATE(Type, Var, Cont)
ITERATE macro to sequence through container elements.
Definition: ncbimisc.hpp:776
int NcbiSys_main(int argc, ncbi::TXChar *argv[])
static unsigned int StringToUInt(const CTempString str, TStringToNumFlags flags=0, int base=10)
Convert string to unsigned int.
Definition: ncbistr.cpp:543
void SetSql(CTempString sql)
Set current sql statement.
Definition: sdbapi.cpp:3792
static CStringUTF8 AsUTF8(const CTempString src, EEncoding encoding, EValidate validate=eNoValidate)
Convert into UTF8 from a C/C++ string.
Definition: ncbistr.hpp:5612
void AddPositional(const string &name, const string &comment, EType type, TFlags flags=0)
Add description for mandatory positional argument.
Definition: ncbiargs.cpp:2284
static string query
void ParseArgs(void)
double f(double x_, const double &y_)
Definition: njn_root.hpp:188
char TXChar
Definition: ncbistr.hpp:169
virtual int Run(void)
Run the application.
virtual void Init(void)
Initialize the application.
Database connection object.
Definition: sdbapi.hpp:1075
void VerifyDone(EHowMuch how_much=eThisResultSet)
Ensure that no unread rows remain, and that the total number of rows satisfies any constraints specif...
Definition: sdbapi.cpp:3884
string AsString(const CTimeFormat &format=kEmptyStr, TSeconds out_tz=eCurrentTimeZone) const
Transform time to string.
Definition: ncbitime.cpp:1515
static char sql[1024]
Definition: putdata.c:45
virtual const CArgs & GetArgs(void) const
Get parsed command line arguments.
Definition: ncbiapp.cpp:190
CTime –.
Definition: ncbitime.hpp:290
CArgDescriptions –.
Definition: ncbiargs.hpp:514
An arbitrary string.
Definition: ncbiargs.hpp:563
const CNcbiArguments & GetArguments(void) const
Get the application's cached unprocessed command-line arguments.
Definition: ncbiapp.hpp:681
void ExecuteSP(CTempString sp, const CTimeout &timeout=CTimeout(CTimeout::eDefault))
Execute stored procedure with given name.
Definition: sdbapi.cpp:3804
CArgs –.
Definition: ncbiargs.hpp:356
Defines the CNcbiApplication and CAppException classes for creating NCBI applications.
Parameter can be returned from stored procedure.
Definition: sdbapi.hpp:176
Convenience class to initialize database connection parameters from URL-like strings and/or applicati...
Definition: sdbapi.hpp:875
CNcbiApplication –.
Definition: ncbiapp.hpp:120
void DemoSqlInjection(void)
void SetUsageContext(const string &usage_name, const string &usage_description, bool usage_sort_args=false, SIZE_TYPE usage_width=78)
Set extra info to be used by PrintUsage().
Definition: ncbiargs.cpp:3110
const CField & GetParameter(CTempString name)
Get value of the parameter.
Definition: sdbapi.cpp:3774
CSDB_ConnectionParam & Set(EParam param, const string &value, TSetFlags flags=0)
Set one of the "essential" database connection parameters, unless overridden in a configuration file...
Definition: sdbapi.hpp:1468
CQuery & SingleSet(void)
Convert this query to work like only one result set was returned effectively merging all result sets ...
Definition: sdbapi.cpp:3816
Named service, interfaces-file alias, or raw server name, per http://ncbi.github.io/cxx-toolkit/pages...
Definition: sdbapi.hpp:957
static CStringUTF8 SQLEncode(const CStringUTF8 &str)
SQL-encode string.
Definition: ncbistr.cpp:5802
Object used to execute queries and stored procedures on the database server and retrieve result sets...
Definition: sdbapi.hpp:200
USING_NCBI_SCOPE
Modified on Sun Jul 23 20:14:59 2017 by modify_doxy.py rev. 533848