Sunday, August 6, 2023

Bots Usage in Artificial Intelligence

The usage of bots in artificial intelligence (AI) has gained significant attention and importance in recent years. Bots, also known as chatbots or intelligent agents, are software applications designed to perform automated tasks or engage in conversations with humans; they are an integral part of AI systems, enabling interactions and delivering various functionalities across various industries and domains. A bot is a computer program or software application designed to perform specific tasks autonomously or interact with users through conversational interfaces (What is a bot?, n.d.). Bots can be programmed to execute various functions, from providing customer support and answering queries to automating repetitive tasks and gathering information. Artificial intelligence is a term for developing intelligent machines and systems capable of performing tasks traditionally performed by humans; it involves the simulation of human intelligence in devices, enabling them to learn, reason, understand natural language, perceive their environment, and make decisions (What Is Artificial Intelligence?, 2023). Overall, using bots in artificial intelligence has revolutionized various industries by enhancing customer experiences, streamlining processes, and leveraging data-driven insights. As technology advances, the capabilities of bots continue to expand, enabling more sophisticated interactions and driving innovation in AI applications.

Bots History

Bots and their evolution in the context of artificial intelligence have a rich history. The concept of bots can be traced back to the early days of computing. In the 1960s, Joseph Weizenbaum developed ELIZA, a program that could simulate conversations and mimic a Rogerian psychotherapist. ELIZA used pattern-matching techniques to respond to user inputs, creating the illusion of understanding (Jarow, 2023). In the following years, rule-based systems became famous for building bots; these systems used predefined rules and decision trees to process user inputs and generate appropriate responses. Although limited in their capabilities, rule-based bots found applications in customer service and information retrieval.

The rise of the internet and natural language processing (NLP) techniques led to the development of chatbots. In the late 1990s and early 2000s, chatbots like ALICE and SmarterChild gained popularity (Osuch, 2022). These bots utilized pattern matching, keyword recognition, and simple rule-based approaches to engage in text-based conversations. The advancement of machine learning and AI techniques in the last decade revolutionized the capabilities of bots; rather than relying solely on predefined rules, bots began to employ algorithms that could learn and adapt from data; this allowed for more sophisticated bots to understand context, generate more natural language responses, and provide personalized experiences. Virtual assistants such as Apple’s Siri, Amazon’s Alexa, Google Assistant, and Microsoft’s Cortana emerged as prominent examples of AI-powered bots; these assistants utilize various AI techniques, including natural language understanding, speech recognition, and machine learning to provide users with information, perform tasks, and control smart devices. With the rise of social media platforms, bots have become prevalent in online interactions. Social media bots can automate tasks, engage with users, and disseminate information, however, they have associated malicious activities, such as spreading misinformation and manipulating public opinion with themselves (Yazan Boshmaf).

Scholars have extensively explored the advancements in natural language processing and understanding that have contributed to the improved conversational abilities of bots, techniques such as deep learning, recurrent neural networks, and transformer models have been investigated and applied to enhance bot performance. Researchers have emphasized the importance of personalization and contextual understanding in bot interactions; by leveraging user data and employing reinforcement learning and contextual embeddings, bots can tailor responses to individual users and provide more relevant and meaningful interactions.

Scholars call for responsible bot development practices and integrating ethical considerations into the design and deployment process; studies also explored user experiences and perceptions of bots; usefulness, trust, transparency, and satisfaction influence user acceptance. Researchers have examined different design strategies, conversational styles, and user interfaces to enhance the user experience and foster positive user-bot interactions (Watson, 2022). There is ongoing research and debate surrounding the impact of bots on social media platforms and their potential to manipulate public opinion, spread misinformation, and influence political discourse (Yazan Boshmaf). Scholars continue to investigate detection techniques, countermeasures, and policy implications. AI Bots have immense potential in various industries; future research and practical applications should strive to address ethical concerns, enhance user experiences, and drive responsible and beneficial bot usage in artificial intelligence.

.

Types and Applications of Bots in Artificial Intelligence

There are multiple types of bots that are in use today in various industries. The following list will define some of the most important types.

  • Chatbots: Chatbots are designed to interact with users through text or voice-based conversations; they can be found in various applications, including customer support, virtual assistants, and messaging platforms. Chatbots can use rule-based systems or machine-learning techniques to understand user queries and generate appropriate responses.
  • Social Media Bots: Social media bots are designed to automate tasks and engage with users on social media platforms; they can perform various activities, such as posting content, liking or sharing posts, following users, and sending messages, while some social media bots serve legitimate purposes, others may be used for malicious activities, such as spreading spam or misinformation.
  • Autonomous Bots: Autonomous bots, also known as intelligent agents, are designed to operate independently and make decisions based on their environment or predefined goals, these bots can perceive their surroundings, gather information, and take action to achieve specific objectives. Autonomous bots can be employed in diverse domains, including robotics, autonomous vehicles, and virtual environments.
  • Recommendation Bots: Recommendation bots utilize AI algorithms to analyze user preferences, behavior, and historical data to provide personalized recommendations; these bots are commonly used in e-commerce, streaming platforms, and content delivery systems. Recommendation bots aim to enhance the user experience by suggesting products, movies, music, or articles tailored to individual interests.
  • Trading Bots: Trading bots, also known as algorithmic trading bots or robo-advisors, use AI techniques to automate financial trading decisions; these bots analyze market data, monitor trends, and execute trades based on predefined strategies. Trading bots aim to optimize trading efficiency, reduce human error, and capitalize on market opportunities.
  • Virtual Assistants: Virtual assistants, such as Apple’s Siri, Amazon’s Alexa, Google Assistant, and Microsoft’s Cortana, are AI-powered bots designed to provide users with information, perform tasks, and assist with various activities. Virtual assistants use voice recognition, natural language processing, and machine learning algorithms to understand user queries and execute commands.
  • Gaming Bots: Gaming bots are AI agents designed to play games autonomously or assist human players; they can compete against human players or other bots, solve puzzles, and navigate game environments. Gaming bots can employ reinforcement learning and neural networks to learn and improve their gameplay strategies.
  • Information Retrieval Bots: Information retrieval bots are designed to retrieve specific information from vast data sources; these bots can search databases, websites, or knowledge bases to provide users with relevant information based on their queries. Search engines, recommendation systems, and question-answering applications commonly use information retrieval bots.

It is important to note that these categories are not mutually exclusive, and there can be overlap between different types of bots. Additionally, the field of bot development and AI is continuously evolving, leading to the emergence of new types of bots with unique functionalities and applications.

Case Studies:

Bots have found applications in various industries, revolutionizing the way businesses operate and interact with customers; below are some case studies from different service industries,

Customer Service

Bank of America’s Erica – Bank of America introduced Erica, an AI-powered virtual assistant, to enhance customer service. Erica assists customers with account balance inquiries, transaction history, bill payments, and budgeting; by leveraging Erica, Bank of America reported increased customer engagement and a significant reduction in call center volume, leading to cost savings and improved customer satisfaction (SCHWARTZ, 2021).

Healthcare:

Babylon Health’s AI Chatbot – Babylon Health developed an AI chatbot that offers symptom checking and medical advice. The chatbot uses machine learning algorithms and a large medical database to provide users with personalized health assessments. In a study published in The Lancet, Babylon’s chatbot demonstrated a diagnostic accuracy comparable to human doctors in primary care settings, highlighting its potential for providing accessible healthcare services (Olsen, 2022).

Finance

Wealthfront’s Robo-Advisor – Wealthfront is an automated investment service that utilizes a robo-advisor. The platform collects user information, including financial goals and risk tolerance, and recommends customized investment portfolios. Wealthfront’s robo-advisor has attracted a significant user base and is known for its low-cost investment options and user-friendly interface (Tepper, 2023).

Marketing

Sephora’s Virtual Artist – Sephora, a cosmetics retailer, introduced the Virtual Artist bot, which uses augmented reality (AR) technology to allow users to try on different makeup products virtually; this bot analyzes facial features and applies makeup digitally, enabling customers to visualize different products before making a purchase. Sephora reported increased user engagement, higher conversion rates, and improved customer satisfaction after implementing the Virtual Artist bot (Rayome, 2018).

E-commerce

H&M’s Chatbot Stylist – H&M developed a chatbot stylist that assists customers with fashion advice and outfit suggestions. Users can interact with the chatbot through messaging platforms and receive personalized recommendations based on their preferences, style, and occasion; this chatbot enhances the shopping experience, increases customer engagement, and drives sales by providing tailored fashion guidance (Global, 2021).

Travel and Hospitality

Marriott International’s Chatbots – Marriott International implemented chatbots across multiple channels to enhance the guest experience. Guests can use the chatbots to make room reservations, request services, and obtain information about hotel amenities; these chatbots provide real-time responses, improve efficiency in handling guest inquiries, and contribute to higher guest satisfaction scores (Schick, 2017)

These examples highlight the positive impact of bots in various applications; they showcase how bots can streamline processes, improve customer experiences, and deliver personalized services, ultimately driving business growth and customer satisfaction.

Advantages of Bot Usage

Bots can automate repetitive and mundane tasks, freeing up human resources to focus on more complex and value-added activities; they can handle large volumes of requests simultaneously, providing quick and consistent responses; this increased efficiency leads to improved productivity and reduced turnaround times. Bots can offer cost savings by reducing the need for extensive human labor; once developed and deployed, bots can handle tasks 24/7 without the need for breaks or overtime pay.

Cost-effectiveness is particularly beneficial in customer service, where bots can handle routine inquiries, reducing the load on human support agents. Bots can quickly scale their operations to handle high volumes of requests without significant additional resources. Whether customer inquiries, order processing, or data analysis, bots can handle increased workloads without experiencing fatigue or performance degradation (Linda Erlenhov, 2020). Bots also can provide consistent and accurate responses, ensuring a uniform customer experience; they can access and analyze vast amounts of data quickly, leading to more accurate and relevant information retrieval and decision-making; this reliability is particularly valuable in applications like healthcare diagnosis or financial analysis.

Limitations and Challenges

Bots raise ethical concerns, particularly in data privacy, transparency, and accountability. Care must be taken to ensure that bots respect user privacy, handle personal data responsibly, and adhere to legal and ethical guidelines; transparency in bot interactions is crucial to maintain trust and avoid deceptive practices (MAIRIELI WESSEL, 2021).

Bots often require access to user data to deliver personalized experiences and perform their tasks effectively, however, this raises privacy concerns as users may be hesitant to share sensitive information with bots (Zeineb Safi, 2020).

Implementing robust security measures and obtaining user consent to address these privacy concerns is essential. Bots learn from data, and if the training data contains biases or reflects societal prejudices, the bots may unintentionally amplify those biases in their responses. Bias detection and mitigation techniques are necessary to ensure fair and unbiased outcomes when deploying bots, especially in sensitive domains like hiring or decision-making.

Editor note: For more information about overcoming bias, check out Yifei Wang’s “Beyond Personalization, Overcoming Bias in Recommender Systems”:

Risks and Drawbacks of Overreliance on Bots

Overreliance on bots may result in a loss of human touch and personalized interactions; some customers prefer human assistance, especially in complex or emotionally sensitive situations, and striking the right balance between bot automation and human support is crucial to meet diverse customer needs. While bots have advanced significantly, they may still struggle with understanding complex or ambiguous queries and context. They may provide inaccurate or incomplete responses, leading to frustration for users. Human intervention may be required to handle such situations, which can limit the full automation potential. Bots operate based on predefined rules or learned patterns; they may struggle with handling unforeseen scenarios or adapting to rapidly changing circumstances. situations requiring creativity, intuition, or complex decision-making may require human judgment and intervention.

It is essential to recognize these limitations and address them through continuous improvement, responsible development practices, and ongoing monitoring of bot performance to ensure their practical and ethical usage in AI applications.

Technical Aspects and Development of Bots

In bot development, various underlying technologies and algorithms are used to enable effective communication and decision-making. Two key components in bot development are natural language processing (NLP) and machine learning (ML).

  • Natural Language Processing: NLP begins with tokenization, where sentences or paragraphs are divided into smaller units called tokens, such as words or sub-words. Tokenization helps in breaking down the text into manageable components for analysis; the part-of-speech tagging technique assigns grammatical labels (such as nouns, verbs, and adjectives) to each token, aiding in understanding the syntactic structure of a sentence.

    Named Entity Recognition (NER) identifies and classifies named entities, such as person names, locations, organizations, or dates, within the text; it helps in extracting meaningful information from unstructured data (Gruetzemacher, 2022). Sentiment analysis determines the emotional tone or sentiment expressed in a piece of text; it is used to gauge the sentiment as positive, negative, or neutral, which can be valuable in customer feedback analysis and social media monitoring.

  • Machine Learning: Supervised learning involves training a model on labeled data, where inputs are associated with corresponding outputs. In bot development, supervised learning algorithms can be used for tasks like intent classification (identifying the purpose of a user’s query) and named entity recognition (identifying specific entities within a sentence).

    Unsupervised learning algorithms are used when labeled data is not available; they discover patterns or structures in the data without explicit guidance; for example, clustering algorithms can be applied to group similar user queries together, aiding in organizing data or identifying user segments (Linda Erlenhov, 2020).

    Reinforcement learning involves training an agent to interact with an environment and learn optimal actions based on rewards and penalties; it can be utilized to develop bots capable of learning from user interactions and improving their decision-making over time. Deep learning uses artificial neural networks with multiple layers to learn complex patterns from data.

    Convolutional Neural Networks (CNNs) are commonly used for tasks involving image analysis, while Recurrent Neural Networks (RNNs) and Transformers are effective for sequential data, such as text or speech. Deep learning has significantly advanced various aspects of bot development, including language understanding, dialogue generation, and image recognition.

These underlying technologies and algorithms form the foundation of bot development, enabling bots to understand user inputs, extract relevant information, and generate appropriate responses. Combining NLP and ML techniques allows bots to communicate effectively and interact intelligently with users.

Building and training bots:

Building and training bots involve several key steps; data collection, preprocessing, and model selection. Below are the main steps involved:

  1. Define Bot Objectives: Clearly define the objectives and purpose of the bot. Determine the specific tasks it needs to perform, such as answering FAQs, providing recommendations, or handling transactions.
  2. Data Collection: Gather relevant data to train and develop the bot, including historical customer interactions, user queries, or publicly available datasets. Data can be collected from websites, social media platforms, customer support logs, or existing databases.
  3. Data Preprocessing: Clean the collected data to remove noise, errors, and redundant information; this involves tasks such as removing duplicate entries, handling missing values, and correcting inconsistencies. Transform the raw text data into a format suitable for analysis; steps may include tokenization (breaking text into words or sub-words), removing stop words, handling special characters, and normalizing the text by applying techniques like stemming or lemmatization (Gaddam, 2017).
  4. Data Annotation (Optional): In supervised learning scenarios where labeled data is required, data annotation is necessary. Experts or annotators label the data with relevant tags or classes, indicating the intent, sentiment, or named entities in the text. Annotation can be done manually or with the help of automated tools.
  5. Model Selection: Select appropriate models and algorithms based on the bot’s objectives and the nature of the task; this involves choosing the appropriate machine learning algorithms, neural network architectures, or pre-trained models that best suit the bot’s requirements, consider factors such as the complexity of the task, available computational resources, and the size and quality of the dataset.
  6. Training the Bot: Divide the preprocessed data into training, validation, and test sets; the training set is used to train the bot, the validation set helps optimize model parameters, and the test set evaluates the final performance of the trained bot. Extract relevant features from the data to represent the input to the model effectively; this may involve techniques like bag-of-words, TF-IDF (Term Frequency-Inverse Document Frequency), or word embeddings to capture semantic meaning. Train the selected model using the training data; this involves feeding the input features into the model, adjusting its parameters, and optimizing the loss function to minimize errors or maximize performance metrics. Assess the performance of the trained model using the validation set. Evaluate metrics such as accuracy, precision, recall, F1 score, or area under the curve (AUC) to gauge the performance of the model (Gaddam, 2017).
  7. Model Fine-Tuning and Iteration: Analyze the model’s performance and fine-tune hyperparameters, architecture, or preprocessing steps if necessary; this iterative process helps optimize the bot’s performance and ensures it meets the desired objectives.
  8. Deployment and Testing: Once the model is trained and validated, deploy the bot in a production environment, and test its functionality, performance, and integration with other systems. Monitor and collect user feedback to identify areas for improvement (Gaddam, 2017).

Throughout the development process, it is essential to maintain documentation, perform version control, and adhere to ethical guidelines, ensuring transparency, fairness, and privacy considerations are addressed; by following these key steps, developers can build and train bots that effectively understand user inputs, provide accurate responses, and deliver a seamless user experience (Linda Erlenhov, 2020).

Ethical and Legal Implications

Using bots in various applications raises important ethical considerations that need to be addressed to ensure responsible and fair deployment. Bots should strive to be transparent about their nature and capabilities; users should be informed that they are interacting with a bot rather than a human, which fosters trust and sets appropriate expectations. Additionally, when bots make decisions or provide responses, they should offer explanations or justifications to the user, helping them understand the underlying processes and building user confidence.

Bot developers and organizations deploying bots should take responsibility for the actions and decisions of their bots; this includes addressing issues such as the accuracy and reliability of the information provided by bots, ensuring compliance with legal and regulatory requirements, and taking measures to prevent the misuse of bots for harmful or malicious purposes (Evan Nadel, 2019).

Bots can inadvertently perpetuate biases present in the data they are trained on, leading to biased or unfair outcomes; bias can occur in various forms, including racial, gender, or socio-economic biases; it is crucial to mitigate such biases during bot development by carefully curating training data, using diverse datasets, and regularly auditing and monitoring the performance of bots to identify and rectify any bias-related issues. Bots often process and store user data, making privacy and data protection critical concerns; organizations must ensure that appropriate data protection measures are in place, including obtaining user consent, anonymizing or pseudonymizing data when possible, securely storing and transmitting data, and adhering to applicable privacy regulations (Zakir, n.d.).

Users should have clear information about how bots collect, store, and use their data. Organizations should seek user consent for data collection and provide options for users to control the extent of data sharing and the use of their personal information; bots should not engage in deceptive or manipulative behaviors that exploit users’ vulnerabilities or trust. Developers should adhere to ethical guidelines that prevent bots from engaging in activities that could harm users or manipulate their behavior; continuous monitoring and evaluation of both performance and user feedback are essential to identify and address ethical concerns that may arise during the deployment of bots. Regular audits and assessments can help detect and rectify ethical issues, ensuring that bots align with ethical standards and user expectations.

Addressing these ethical considerations requires a combination of technical measures, organizational policies, and regulatory frameworks. Bot developers and organizations must be proactive in designing and deploying bots that prioritize transparency, fairness, and user well-being, fostering trust and responsible use of AI technologies.

Legal frameworks and regulations:

Legal frameworks and regulations governing bot development and deployment vary across different jurisdictions.

Privacy Laws and Data Protection: General Data Protection Regulation (GDPR), applicable in the European Union (EU) and European Economic Area (EEA), sets standards for the collection, storage, and processing of personal data. Bots that handle user data must comply with GDPR requirements, including obtaining user consent, ensuring data security, providing transparency about data usage, and offering users the right to access, rectify, and erase their data. California Consumer Privacy Act (CCPA) imposes obligations on businesses that collect or sell personal information of California residents; bots operating in California or handling data of California residents must comply with CCPA requirements, which include disclosing data collection practices, granting users the right to opt-out of data sales, and providing mechanisms for users to request access to and deletion of their personal information (Evan Nadel, 2019). Many countries have their own data protection laws, such as the Personal Information Protection and Electronic Documents Act (PIPEDA) in Canada and the Personal Data Protection Act (PDPA) in Singapore; these laws define rules for handling personal data and may impose specific requirements on bot developers and operators.

Intellectual Property Rights: Bot developers must respect copyright and trademark laws when designing bots; unauthorized use of copyrighted content, logos, or trademarks can lead to legal implications; bots should be developed to avoid infringing upon intellectual property rights (Zakir, n.d.).

Consumer Protection: Bots must comply with laws that prohibit unfair or deceptive practices in consumer interactions; bots should not engage in misleading behaviors, false advertising, or misrepresentation that could harm consumers (Subhajit Basu, 2018).

Sector-Specific Regulations:

Financial Industry: Bots used in financial services may need to comply with specific regulations such as the Know Your Customer (KYC) and Anti-Money Laundering (AML) requirements. Regulations like the Payment Services Directive (PSD2) in the EU also govern the use of bots in financial transactions and require specific security measures.

Healthcare: Bots used in healthcare must comply with regulations such as the Health Insurance Portability and Accountability Act (HIPAA) in the United States, which governs the privacy and security of patient’s health information.

Liability and Accountability:

Legal responsibility for the actions of bots can be complex, depending on the jurisdiction; liability may fall on the bot developer, the organization deploying the bot, or both; clear contractual agreements and terms of service can help define the responsibilities and limitations of liability. Both developers and organizations must consult with legal professionals and stay updated on the relevant laws and regulations in the jurisdictions where their bots are developed or deployed (Pavel P Baranov, 2019). Compliance with applicable legal frameworks helps ensure the responsible and lawful development and deployment of bots while protecting the rights and interests of users and consumers.

Future Directions and Challenges:

The field of bot usage in artificial intelligence is evolving rapidly, and several emerging trends and potential future developments are shaping its trajectory. Key areas to focus on,

Conversational AI Advancements: Conversational AI aims to develop more human-like and natural interactions between bots and users; future developments may include improved language understanding, context-aware responses, and the ability to handle complex dialogues, emotions, and sarcasm. Advancements in language models, such as incorporating world knowledge and commonsense reasoning, could contribute to more sophisticated and engaging conversations.

  • Multimodal Interactions: Bots that can process and generate multiple modalities like text, speech, images, and videos are gaining traction; future developments may involve enhancing bots’ ability to understand and generate responses using different modalities simultaneously, enabling more immersive and interactive experiences.
  • Personalization and User Adaptation: The future of bot development will likely focus on personalization and user adaptation; bots may leverage user preferences, historical interactions, and contextual information to deliver tailored and personalized experiences. Advanced user modeling techniques and reinforcement learning approaches could enable bots to adapt their behavior and responses based on individual user characteristics and preferences (Yazan Boshmaf).
  • Integration with IoT and Smart Devices: Bots are expected to play a significant role in the Internet of Things (IoT) ecosystem, where they can interact with and control various smart devices. Integration with IoT devices like smart home assistants, wearables, and connected appliances could enable bots to perform tasks such as home automation, health monitoring, and personalized recommendations.
  • Enhanced Bot Collaboration: Bots that collaborate with each other to solve complex tasks or provide comprehensive services hold promise; future developments may involve creating bot ecosystems where specialized bots work together, leveraging their unique capabilities and knowledge, to provide more comprehensive and seamless user experiences (Watson, 2022).
  • Ethical and Responsible AI: As the use of bots expands, there will be a growing emphasis on ethical and responsible AI practices; future developments may include the integration of fairness, interpretability, and explainability techniques into bot systems to ensure transparency, reduce biases, and enable users to understand the basis of both decisions.
  • Augmented Intelligence: The future of bot usage may involve augmenting human intelligence with bot capabilities; bots can assist humans in tasks by providing real-time information, automating repetitive processes, and offering decision support. Augmented intelligence systems that combine the strengths of humans and bots have the potential to enhance productivity and problem-solving across various domains significantly.
  • Social and Emotional Intelligence: Advancements in understanding social cues, emotions, and empathy could lead to the development of bots that exhibit greater social and emotional intelligence, bots with emotional understanding capabilities could offer empathetic responses, provide mental health support, or assist in social contexts where emotional intelligence is essential.

These emerging trends and potential future developments in bot usage indicate a shift toward more sophisticated, personalized, and socially aware bot systems. As AI technologies continue to advance, these developments have the potential to reshape various industries, transform user experiences, and enable new applications for bots in diverse domains.

Conclusion

Bots play a significant role in artificial intelligence by automating tasks, providing information, and engaging with users. The advantages of using bots in AI include increased efficiency, cost-effectiveness, scalability, and round-the-clock availability. Bots have limitations and challenges, including ethical considerations, privacy concerns, potential biases, and the risk of overreliance on bots in certain contexts.

Future research should focus on improving bot-human interaction, enhancing natural language understanding, and developing more human-like and contextually aware responses. Challenges like ethical consideration and data privacy need to be addressed at the forefront of both developments to ensure responsible and fair deployment. Collaborative research efforts and interdisciplinary approaches are crucial for advancing the field of bot development and deploying bots in a manner that aligns with user needs, societal values, and ethical standards.

Overall, bots have emerged as powerful tools in the field of artificial intelligence, with the potential to revolutionize various industries and enhance user experiences. Continued research and responsible deployment of bots can unlock new opportunities, drive innovation, and address challenges to maximize their positive impact.


Bibliography

Evan Nadel, N. P. (2019, November). Data Collection & Management, Professional Perspective – Legal Implications of Using AI, Biometrics, or Bots in the Workplace. Opgehaald van https://www.bloomberglaw.com/external/document/XEEJ0U5S000000/data-collection-management-professional-perspective-legal-implic

Gaddam, K. (2017). Building Bots with Microsoft Bot Framework. Packt Publishing Chatbots .

Global, M. O. (2021, March 11). Conversational AI in eCommerce: 9 of the Most Successful Chatbot Examples. Opgehaald van https://masterofcodeglobal.medium.com/conversational-ai-in-ecommerce-9-of-the-most-successful-chatbot-examples-89bc5e1569b3

Gruetzemacher, R. (2022, April 19). AI And Machine Learning. Opgehaald van https://hbr.org/2022/04/the-power-of-natural-language-processing

How does an AI chatbot work? (sd). Opgehaald van https://powervirtualagents.microsoft.com/en-us/ai-chatbot/

Jarow, O. (2023, March 5). How the first chatbot predicted the dangers of AI more than 50 years ago. Opgehaald van https://www.vox.com/future-perfect/23617185/ai-chatbots-eliza-chatgpt-bing-sydney-artificial-intelligence-history

Linda Erlenhov, F. G. (2020). An Empirical Study of Bots in Software Development: Characteristics and Challenges from a Practitioner’s Perspective. ACM Joint European Software Engineering Conference and Symposium on the Foundations of Software Engineering (ESEC/FSE). arXiv:2005.13969.

MAIRIELI WESSEL, I. W. (2021). Don’t Disturb Me: Challenges of Interacting with Software Bots on Open Source Software Projects. J. ACM, Vol. 37, No. 4, Article 111. Publication date: August 2021.

Mondal, A. (2023, June 26). Complete Guide to Build Your AI Chatbot with NLP in Python. Opgehaald van https://www.analyticsvidhya.com/blog/2021/10/complete-guide-to-build-your-ai-chatbot-with-nlp-in-python/

Olsen, E. (2022, March 31). Babylon’s AI-enabled symptom checker added to recently acquired Higi’s app. Opgehaald van https://www.mobihealthnews.com/news/babylons-ai-enabled-symptom-checker-added-recently-acquired-higis-app

Osuch, W. (2022, April 19). Chatbots: A Brief History Part I – 1960s to 1990s. Opgehaald van https://www.botsplash.com/post/chatbots-a-brief-history

Pavel P Baranov, A. Y. (2019, 12 27). Problems of Legal Regulation of Robotics and Artificial Intelligence from the Psychological Perspective. Opgehaald van https://files.eric.ed.gov/fulltext/EJ1280119.pdf

Rayome, A. D. (2018, February 15). How Sephora is leveraging AR and AI to transform retail and help customers buy cosmetics. Opgehaald van https://www.techrepublic.com/article/how-sephora-is-leveraging-ar-and-ai-to-transform-retail-and-help-customers-buy-cosmetics/

Salton, G. (1966). ELIZA—a computer program for the study of natural language communication between man and machine. Communications of the ACM.

Schick, S. (2017, November 8). Marriott makes more room for chatbots to enhance guest experiences. Opgehaald van https://www.marketingdive.com/news/marriott-makes-more-room-for-chatbots-to-enhance-guest-experiences/510117/

SCHWARTZ, E. H. (2021, April 21). Bank of America’s Virtual Assistant Erica Explodes in Popularity. Opgehaald van https://voicebot.ai/2021/04/21/bank-of-americas-virtual-assistant-erica-explodes-in-popularity/

Subhajit Basu, A. O. (2018, May 8). Legal framework for small autonomous agricultural robots. Opgehaald van https://link.springer.com/article/10.1007/s00146-018-0846-4

Tepper, T. (2023, March 24). Wealthfront Review 2023. Opgehaald van https://www.forbes.com/

Watson, I. (2022, September 22). The ultimate guide to machine-learning chatbots and conversational AI. Opgehaald van https://www.ibm.com/watson-advertising/thought-leadership/machine-learning-chatbot

Weizenbaum, J. (1966, January). ELIZA–A Computer Program For the Study of Natural Language Communication Between Man and Machine. Opgehaald van https://redirect.cs.umbc.edu/courses/331/papers/eliza.html

What is a bot? (sd). Opgehaald van https://ift.tt/SqZy3Vo

What Is Artificial Intelligence? (2023, January 5). Opgehaald van https://www.indianai.in/what-is-artificial-intelligence/

Yazan Boshmaf, I. M. (sd). Key Challenges in Defending Against Malicious Socialbots. University of British Columbia, Vancouver, Canada.

Zakir, T. (sd). Opgehaald van ChatGPT: The Bot That Needs a Chat About Regulation: https://www.nyujlb.org/single-post/chatgpt-the-bot-that-needs-a-chat-about-regulation

Zeineb Safi, A. A.-A. (2020). Technical Aspects of Developing Chatbots for Medical Applications: Scoping Review. Journal of Medical Internet Research.

 

The post Bots Usage in Artificial Intelligence appeared first on Simple Talk.



from Simple Talk https://ift.tt/jZWBVdl
via

Friday, August 4, 2023

Building REST APIs in Go with Mux and GORM

In modern software development, Application Programming Interfaces (APIs) are essential for building scalable and flexible systems. APIs enable applications to communicate and exchange data, amongst other actions.

Representational State Transfer (REST) is an API specification for building simple, scalable, client-server APIs based on HTTP, stateless, cacheable, and layered.

RESTful APIs provide a medium for interaction between software components, easing the building process of distributed applications. However, building RESTful APIs can be daunting, especially for beginners; that’s where the Gorilla Mux package comes in handy.

The Gorilla Mux package is a powerful HTTP router and dispatcher that simplifies building RESTful APIs. Gorilla Mux allows you to define routes for different HTTP methods (GET, POST, PUT, DELETE, etc.) and handle requests and responses in a structured manner. The Gorilla Mux package is easy to use, and the package supports a range of middleware that you can use for logging and authentication operations, among others.

Getting Started Building Applications in Go

Getting started with building REST APIs with Go is quite easy. First, you must install the latest Go version on your system. The installation process is straightforward. Simply visit the Go downloads webpage to install the Go binary distribution for your computer’s operating system.

Run this command on your terminal to verify that you’ve successfully installed Go.

go version

Once you have installed Go, the next step is creating a new directory and initializing the directory as a Go project.

Run these commands on your terminal to create and initialize a new Go project on your computer.

mkdir HumanAPI && cd HumanAPI
go mod init HumanAPI

The go mod command initializes a new Go project in the specified working directory. The command also creates a go.mod file in the directory for managing your project’s dependencies.

Finally, you’ll need some form of persistence for your database. In this tutorial, you’ll learn how to use GORM and a SQL database for persistence for your API. GORM supports a variety of SQL databases, including MSSQL, MySQL, and PostgreSQL. Install your preferred database management system, and you’re good to go.

Building APIs with Gorilla Mux

The Gorilla Mux package is a third-party package, and you’ll need to install the package as one of your project’s dependencies to get started.

Run this command in the terminal of your project’s working directory to install the Gorilla Mux package.

go get github.com/gorilla/mux

Here’s how to import the Gorilla Mux package into your Go files.

import "github.com/gorilla/mux"

You’ll need to create a new router instance to work with handler functions with the Gorilla Mux package. You can use the NewRouter function to create a new router instance.

router := mux.NewRouter()

After creating a router instance, you can use the HandleFunc function to map handler functions to routes and specify the HTTP method for the routes.

func main() {
        router := mux.NewRouter()
        router.HandleFunc("/humans/{id}",   
                    getHuman).Methods("GET")
        router.HandleFunc("/humans", createHuman).Methods("POST")
        router.HandleFunc("/humans/{id}", 
                    updateHuman).Methods("PUT")
        router.HandleFunc("/humans/{id}", 
                    deleteHuman).Methods("DELETE")
        log.Fatal(http.ListenAndServe(":8080", router))
}

The main function above creates a new router instance and mounts routes to GET, POST, PUT, and DELETE requests before starting a server on port :8080 with the ListenAndServe function of the http package.

Setting Persistence Up GORM for the API

Most of the time, you’ll need persistence when building APIs for data storage and retrieval. GORM is the most popular ORM in the Go ecosystem, and the package is useful for working with many databases.

Run this command to install the GORM package and the SQLite database driver for the package.

go get gorm.io/gorm
go get gorm.io/driver/sqlite //SQLite

You can learn more about GORM from this article if you’re unfamiliar with the package. The article covers the most popular operations with the package while considering other databases.

Here’s the list of imports you’ll need to set up the API, including Gorilla Mux, GORM, and Go built-in packages.

import(
   "encoding/json"
   "fmt"
   "log"
   "net/http"
   "github.com/gorilla/mux"
   "gorm.io/driver/sqlite"
   "gorm.io/gorm"
)

You’ll need a struct model that defines the database schema for queries and data migration. Here’s an example struct for the database schema.

type Human struct {
        ID   uint   `gorm:"primaryKey"`
        Age  int    `json:"age"`
        Name string `json:"name"`
}

The ID field is the primary key, and the Age and Name fields are regular fields.

You’ll need to access your database instance from multiple handler functions, so you can resort to making it a global variable for accessibility. The DB struct defines a GORM database instance.

var (
        db  *gorm.DB
        err error
)

You can use a function to manage database auto migrations. You’ll use the Open function of the gorm package to open a database connection from the driver. The sqlite package’s Open function returns a connection instance that GORM can access and return a database instance.

After creating a database instance, you can use the AutoMigrate function of the database instance that takes in the struct model and auto-migrates struct instances.

func DBConnection() error {
        db, err = gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
        if err != nil {
                return err
        }
        err = db.AutoMigrate(&Human{})
        if err != nil {
                return err
        }
        return nil
}

The DBConnection function is the auto migration function. The function returns an error if there’s any during the migration process.

You can proceed to call the DBConnection function in the main function.

func main() {
        err := DBConnection()
        if err != nil {
                log.Fatal("Database connection error", err)
        }
        router := mux.NewRouter()
        router.HandleFunc("/humans/{id}", 
                 getHuman).Methods("GET")
        router.HandleFunc("/humans", createHuman).Methods("POST")
        router.HandleFunc("/humans/{id}", 
                 updateHuman).Methods("PUT")
        router.HandleFunc("/humans/{id}", 
                 deleteHuman).Methods("DELETE")
        log.Fatal(http.ListenAndServe(":8080", router))
}

The next step is setting up the handler functions for successful operations.

The POST Request Handler Function

Your POST request handler function would receive a JSON request body from the client, decode the JSON into the Human struct instance and migrate the struct to the database using the Create function of the database instance.

func createHuman(writer http.ResponseWriter, 
             request *http.Request) {
        var human Human
        err = json.NewDecoder(request.Body).Decode(&human)
        if err != nil {
                log.Println(err)
        }
        db.Create(&human)
        err := json.NewEncoder(writer).Encode(human)
        if err != nil {
                log.Println(err)
        }
}

The createHuman handler function creates a handler function instance with HTTP response writer and requests instances as arguments, decodes the JSON to struct with the Decode function of the NewDecoder function, migrates the data to the database, and returns an encoded JSON struct to the client.

Here’s a CURL request that makes a POST request to the server for the /humans route.

curl -X POST -H "Content-Type: application/json" 
   -d '{"name":"John Doe","age":30}' <http://localhost:8080/humans>

The GET Request Handler Function

Your GET request handler function will receive an ID from the client’s request, search through the database for a row with the ID and return the row’s data from the database as JSON.

func getHuman(writer http.ResponseWriter, request *http.Request) {
        vars := mux.Vars(request)
        id := vars["id"]
        var human Human
        db.First(&human, id)
        err := json.NewEncoder(writer).Encode(human)
        if err != nil {
                log.Println(err)
        }
}

The getHuman handler function accesses the data from the request using the Vars function of the mux package and retrieves the ID.

The human variable is an instance of the Human struct created to hold the row’s data with the ID, and the Encode function of the NewEncoder function of the JSON package encodes the human struct instance to the client as a response.

Here’s the CURL request that makes a GET to the server requesting data 1 as the ID.

curl <http://localhost:8080/humans/1>

Here’s the result of the request. The request returns the data inserted into the database with the POST request.

The PUT Request Handler Function

The PUT request handler function would retrieve an ID from the request URL and a JSON payload from the request body and update the ID row with the JSON from the request body.

func updateHuman(writer http.ResponseWriter, 
                      request *http.Request) {
        vars := mux.Vars(request)
        id := vars["id"]
        var human Human
        db.First(&human, id)
        err := json.NewDecoder(request.Body).Decode(&human)
        if err != nil {
                log.Println(err)
        }
        db.Save(&human)
        err = json.NewEncoder(writer).Encode(human)
        if err != nil {
                log.Println(err)
        }
}

The updateHuman handler function extracts the ID from the request just like the GET request and retrieves the data with the ID from the database. If the retrieval is successful, the data exists. The Save function of the database instance updates the database with the JSON from the body of the request and returns the JSON to the client after successfully updating the database.

The CURL request for the PUT request holds the payload with different name and age fields from the current data in the database with the ID of 1.

curl -X PUT -H "Content-Type: application/json" 
   -d '{"name":"Jane Doe","age":35}' <http://localhost:8080/humans/1>

On sending the CURL request, here’s the result of the update operation.

The DELETE Request Handler Function

Your DELETE request handler function would retrieve an ID from the request and delete the row with the ID from the database before responding to the client to signal a successful delete operation.

func deleteHuman(writer http.ResponseWriter, 
                      request *http.Request) {
        vars := mux.Vars(request)
        id := vars["id"]
        db.Delete(&Human{}, id)
        _, err := fmt.Fprintf(writer, 
                 "Human with ID = %s has been deleted", id)
        if err != nil {
                log.Println(err)
        }
}

The deleteHuman function retrieves the ID from the client’s request URL and deletes the row using the Delete function of the database instance.

After a successful deletion operation, the function writes a string with the ID to the client.

Here’s the CURL request for the DELETE request. The client requests to delete the row with ID 1.

curl -X DELETE <http://localhost:8080/humans/1>

On sending the CURL request, here’s the result of the update operation.

Conclusion

You’ve learned about RESTful APIs, the Gorilla Mux package, how to use GORM for persistence when building APIs, how to create router instances, mount routes to handler functions, and how to use the Gorilla Mux package to interact with requests.

Using GORM and Gorilla Mux can help you build scalable, maintainable APIs that handle many HTTP requests efficiently.

The post Building REST APIs in Go with Mux and GORM appeared first on Simple Talk.



from Simple Talk https://ift.tt/8ng0Zwq
via

Wednesday, August 2, 2023

Managing Query Store Hints

I wrote about query store hints before when they were first released in Azure SQL databases.

However, there are some tricks related to managing query store hints we will explore on this blog post.

Applying Query Store Hint

When we apply a query store hint, the query already exists in query store, and it has at least one plan registered. It’s even possible to have more.

The query hint will only be applied to further executions of the query, so the existing plans in query store don’t have the query hint applied.

The query store DMVs have relationships to each other, so we can relate the query_store_query_hints DMV with the query plans. However, the result will not identify if the plan was already there before the query hint was created, resulting in a false information about which plan was using which query hint.

The query to retrieve hints and plans is the following:

SELECT query_hint_id,
       qsq.query_id,
       query_hint_text,
       qsq.query_text_id,
       query_sql_text,
       qsp.plan_id,
       query_plan
FROM   sys.query_store_query_hints qsqh
       INNER JOIN sys.query_store_query qsq
               ON qsqh.query_id = qsq.query_id
       INNER JOIN sys.query_store_query_text qsqt
               ON qsq.query_text_id = qsqt.query_text_id
       INNER JOIN sys.query_store_plan qsp
               ON qsq.query_id = qsp.query_id 

Changing the applied hint

When we change the applied hint to a query, the new hint overwrites the previous one. Once again, the new hint will only be applied when the query is compiled again.

As a result, if you immediately use the query to show which plan is using which hint, you will get a completely wrong information.

Retrieving the correct hint applied to each plan

Each plan contains inside its definition, the hint which was used during the plan compilation. If you use the query below, the plan definitions appear in XML format. You can click the definition to open the plan. The Query Hint used with the plan will be in the properties window.

SELECT query_hint_id,
       qsq.query_id,
       query_hint_text,
       qsq.query_text_id,
       query_sql_text,
       qsp.plan_id,
       CONVERT(XML, query_plan) query_plan
FROM   sys.query_store_query_hints qsqh
       INNER JOIN sys.query_store_query qsq
               ON qsqh.query_id = qsq.query_id
       INNER JOIN sys.query_store_query_text qsqt
               ON qsq.query_text_id = qsqt.query_text_id
       INNER JOIN sys.query_store_plan qsp
               ON qsq.query_id = qsp.query_id 

Texto Descrição gerada automaticamente

Uma imagem contendo Interface gráfica do usuário Descrição gerada automaticamente

Using the XML Queries to find the correct Query Hint

Using XML queries over the stored query plans we can extract the actual query hint used during the query compilation.

To make this example better, let’s replace the previous query hint with a new one, using the following query:

EXEC Sp_query_store_set_hints
  @query_id=1,
  @value = N’ OPTION (OPTIMIZE FOR unknown)’

go 

The query, using XML to retrieve the actual query hint in the plan is the following:

WITH xmlnamespaces ( ‘https://ift.tt/vWdzG3s;
AS ns1 )

SELECT
query_hint_id,
qsq.query_id,
query_hint_text,
qsq.query_text_id,
query_sql_text,
qsp.plan_id,
CONVERT(XML, query_plan)
query_plan,
CONVERT(XML, query_plan).value(‘(/ns1:ShowPlanXML/ns1:BatchSequence/ns1:Batch/ns1:Statements/ns1:StmtSimple/@QueryStoreStatementHintText)[1]’, ‘varchar(100)’) actualQueryHint
FROM   sys.query_store_query_hints qsqh
       INNER JOIN sys.query_store_query qsq
               ON qsqh.query_id = qsq.query_id
       INNER JOIN sys.query_store_query_text qsqt
               ON qsq.query_text_id = qsqt.query_text_id
       INNER JOIN sys.query_store_plan qsp
               ON qsq.query_id = qsp.query_id 

This result compares the hint applied in query store, which will be enforced on the next compilation of the plan, with the hint on the current stored plan.

One plan has no hint

The query will very frequently return one plan with no hint. This happens because a combination of reasons:

  • The plan was created before the hint was applied.
  • One of the executions with the hint results in a plan exactly as one already stored. In this case, the stored one is not replaced, staying without the hint application.

This makes especially trick to identify which hint was applied to this plan creation. The only additional option we have is to check the last execution of this plan.

If the plan last execution was before the query hint creation, so the current query hint stored was never applied to the plan. If the plan last execution was after the hint creation, the hint was applied.

To makes things work, query store doesn’t hold date and time of the query hint creation. So, we can check the last time of the plan execution, but we can only guess if it was before or after the query hint application.

The query, with addition of the date:

WITH xmlnamespaces ( ‘https://ift.tt/vWdzG3s; AS ns1 )
SELECT     query_hint_id,
           qsq.query_id,
           query_hint_text,
           qsq.query_text_id,
           query_sql_text,
           qsp.plan_id,
           CONVERT(xml,query_plan)                                                                                                                                      query_plan,
           CONVERT(xml,query_plan).value(‘(/ns1:ShowPlanXML/ns1:BatchSequence/ns1:Batch/ns1:Statements/ns1:StmtSimple/@QueryStoreStatementHintText)[1]’,‘varchar(100)’) actualqueryhint,
           (
                  SELECT max(last_execution_time) last_plan_execution
                  FROM   sys.query_store_runtime_stats
                  WHERE  plan_id=qsp.plan_id) AS last_plan_execution
FROM       sys.query_store_query_hints qsqh
INNER JOIN sys.query_store_query qsq
ON         qsqh.query_id =qsq.query_id
INNER JOIN sys.query_store_query_text qsqt
ON         qsq.query_text_id=qsqt.query_text_id
INNER JOIN sys.query_store_plan qsp
ON         qsq.query_id=qsp.query_id

Summary

Managing query store hints can prove to be trickier than initially imagined. A considerable care is needed when analysing the query store hints applied to each plan.

This blog was inspired by a session delivered by Erin Stellato during the Malta SQL Server 2022 Query Optimization conference

 

The post Managing Query Store Hints appeared first on Simple Talk.



from Simple Talk https://ift.tt/TDEZtfH
via

Monday, July 31, 2023

Running Queries Across Many SQL Server Databases

A challenge that reappears periodically in the world of databases (especially database management) is the need to run code on a subset of databases and to do so in a nuanced manner. Some maintenance or metrics collection processes can be simply run against every database on a server with no ill-effect, but others may be app-specific, or need to omit specific sets of databases.

This article dives into how to create and customize your own solution, tackling everything from filtering databases to validating schema elements to error-handling.

What About sp_MSforeachdb?

The sp_MSforeachdb system stored procedure can be used to run T-SQL across many SQL Server databases. While that sounds ideal, it is an undocumented black box that does not always perform the way you may want it to. Undocumented features can be used with extreme caution, but it is inadvisable to make them a key part of important processes as they may change, be deprecated, or be discontinued with little or no notice.

In addition, sp_MSforeachdb has no ability to be customized or expanded upon. What you see is what you get and there is no flexibility if it does not do precisely what you want.

This article covers some of the things done in a procedure named dbo.RunQueryAcrossDatabases that you can download in a .zip file: RunQueryAcrossDatabases_FullScript.

Code Reusability

The common solution to this problem is to create new code that iterates through databases for each application or process whenever it is needed. While this certainly is functional, it poses all of challenges of code maintainability when the same code is copied into many places.

If a new database appears that is an exception the rules, then each and every process would need to be inspected and adjusted as needed to ensure that the exception is accounted for. Even on a small number of database servers, it is likely that one might be omitted and cause unexpected (and perhaps, not quickly noticed) harm. If hundreds of servers are being maintained, then the odds of missed many become quite high.

A single universal stored procedure that is used for this purpose ensures that when changes are needed, they can be made a single time only:

  • Update the stored proc in source control.
  • Test it in dev/QA.
  • Deploy to production.

Because this process follows the typical one used for deploying application-related code, it is familiar and less likely to result in mistakes or omissions. In addition, a solution that is parameterized can ensure that when changes are needed, they can be made to parameter values and not to source code, minimizing the need for more impactful changes.

Building a Code Execution Solution

To build a solution that executes T-SQL for us, there is value in listing the steps needed to accomplish this task:

  • Create and apply filters to determine which databases should have code executed against them.
  • Create code that will run against that database list.
  • Iterate through that database list.
  • Run the code against each of those databases.

When written out as a list, it becomes clear that this task is far simpler than it sounds, which means that our job of writing this code can move quickly through the necessities and into customization (which is far more fun!)

Note that a complete/working solution is attached at the end of this article. Feel free to download and customize it to your heart’s content!

Create and Customize a Database List

The key to this process is to create a list of databases that code will be run against. This may be as simple as an explicit list of N databases, but more often than not will involve some more detailed logic. For example, there may be a need to filter out all databases that do not meet a specific naming convention. Alternatively we may want to exclude databases by name.

Ultimately, this entire task comes down to querying sys.databases and filtering based on information in the system view. Yeah, you heard that right: SELECT from a view and add a WHERE clause and DONE! OK, it isn’t quite that simple, but we’ll do our best to not make this complex. In order to create this process, dynamic T-SQL will be used. It is possible to do this via standard SQL by creating/modifying a database list step-by-step, adding and removing databases along the way. While this certainly works, I find the resulting code even more convoluted than dynamic SQL (yes, I said that!).

As a brief reminder, sys.databases is a system catalog view that provides a row per database on the server. Included in that data is a hefty amount of metadata and operational information that tells us how a database is configured and its current state. The following query returns some basic (but useful) information from this view:

SELECT
        databases.name,
        databases.database_id,
        databases.create_date,
        databases.compatibility_level,
        databases.collation_name,
        databases.user_access_desc,
        databases.state_desc,
        databases.recovery_model_desc
FROM sys.databases;

The results provide a whole lot of actionable info:

When inspecting databases, knowing the compatibility level, collation, or current state could be critical to making decisions about whether or not to query them and especially apply changes to them. At a higher level, there is value in knowing this information regardless. For example, that BaseballStats database of mine…should that really be set to compatibility level 130 (SQL Server 2016)?! If that were an oversight, then it can be corrected. Similarly, should only two databases be using the FULL recovery model? Another fine question for an administrator/operator to consider.

Note that the database list above will be used for the duration of this article. The databases on your test server, as well as related objects and metadata will vary from what is presented here.

To start, let’s limit the database filtering to database name only. Additional customization based on other details is relatively easy to add once a starter-query has been constructed. To do this, a handful of variables will be introduced:

DECLARE @DatabaseNameLike VARCHAR(100) = 'WideWorldImporters';
DECLARE @DatabaseNameNotLike VARCHAR(100);
DECLARE @DatabaseNameEquals VARCHAR(100);

These filters can be added one by one as filters to sys.databases. If any filter is NULL, it can be ignored:

SELECT
        *
FROM sys.databases
WHERE (@DatabaseNameLike IS NULL 
       OR name LIKE '%' + @DatabaseNameLike + '%')
AND (@DatabaseNameNotLike IS NULL 
       OR name NOT LIKE '%' + @DatabaseNameNotLike + '%')
AND (@DatabaseNameEquals IS NULL OR name = @DatabaseNameEquals);

If @DatabaseNameLike is set to 'WideWorldImporters' and the other variables are left NULL, then the results of the above query would return:

There are no limits to filtering like this. It would be relatively easy to add additional filters for names that start with a prefix, end in a suffix, or any other comparison that can be dreamed up.

Another common need is to omit system databases from queries. Running some T-SQL across many databases may include model, master, tempdb, and msdb, but oftentimes will not. This added filter to the query above can be handled by another variable and one more WHERE clause:

DECLARE @DatabaseNameLike VARCHAR(100) = 'WideWorldImporters';
DECLARE @DatabaseNameNotLike VARCHAR(100);
DECLARE @DatabaseNameEquals VARCHAR(100);
DECLARE @SystemDatabases BIT = 0;
SELECT
        *
FROM sys.databases
WHERE (@DatabaseNameLike IS NULL 
     OR name LIKE '%' + @DatabaseNameLike + '%')
AND (@DatabaseNameNotLike IS NULL 
     OR name NOT LIKE '%' + @DatabaseNameNotLike + '%')
AND (@DatabaseNameEquals IS NULL 
     OR name = @DatabaseNameEquals)
AND (@SystemDatabases = 1 
     OR name NOT IN ('master', 'model', 'msdb', 'tempdb'));

If a search is run using @DatabaseNameLike = 'B' and @SystemDatabases = 1, then the results will look like this:

If the same search is performed, but with @SystemDatabases = 0, then the results are reduced to only a single result:

Sys.databases contains quite a few columns that can be filtered as easily as the name, such as compatibility level, collation name, user access (is it in single or multi-user?), state, recovery model, and much more. Adding filters can be accomplished on any column in the same fashion as above. For example, if there is a need to query all databases on a server – but only those that are online and multi-user, the query above could be adjusted as follows:

DECLARE @DatabaseNameLike VARCHAR(100) = 'WideWorldImporters';
DECLARE @DatabaseNameNotLike VARCHAR(100);
DECLARE @DatabaseNameEquals VARCHAR(100);
DECLARE @SystemDatabases BIT = 0;
DECLARE @CheckOnline BIT = 1;
DECLARE @CheckMultiUser BIT = 1;
SELECT
        *
FROM sys.databases
WHERE (@DatabaseNameLike IS NULL 
       OR name LIKE '%' + @DatabaseNameLike + '%')
  AND (@DatabaseNameNotLike IS NULL 
       OR name NOT LIKE '%' + @DatabaseNameNotLike + '%')
AND (@DatabaseNameEquals IS NULL 
     OR name = @DatabaseNameEquals)
AND (@SystemDatabases = 1 
     OR name NOT IN ('master', 'model', 'msdb', 'tempdb'))
AND (@CheckOnline = 0 
      OR state_desc = 'ONLINE')
AND (@CheckMultiUser = 0 
      OR user_access_desc = 'MULTI_USER');

Run this query, and you will get back WideWorldImportersDW and WideWorldImporters. To test the offline change, let’s set WideWorldImportersDW offline:

ALTER DATABASE WideWorldImportersDW SET OFFLINE;

If you want to make it happen immediately on your local machine, you can add: WITH NO_WAIT ROLLBACK IMMEDIATE; and it will kill existing transactions/connections and apply the ALTER DATABASE command.

Now, run the query and the offline database is removed from the results:

We’ve dove into filtering the database list by metadata in sys.databases. Next up is how to filter based on the existence (or non-existence) of objects.

Validating the Presence of Objects

Another common challenge when executing a query across any number of databases is to only run the query if a specific table or object exists. Some examples of this include:

  • Select data from a table, but only if it exists (or if a specific column in that table exists).
  • Execute a stored procedure, but only if it exists. This prevents throwing “Object Not Found” errors.
  • Validate if an object exists and log details about it, depending on its status.
  • Check if a release completed successfully.

At its core, this is not a challenging task. There are many views available in SQL Server that allow us to check and see if an object exists or not. For example, the following code will check if the table Sales.Customers exists in the WideWorldImporters database:

SELECT
      COUNT(*) AS DoesItExist
FROM WideWorldImporters.sys.tables
INNER JOIN WideWorldImporters.sys.schemas
ON tables.schema_id = schemas.schema_id
WHERE tables.name = 'Customers'
AND schemas.name = 'Sales';

The result is straightforward, since there is one table name Sales.Customers in the WideWorldImporters database:

What I want is for the logic behind this to be encapsulated into the process we are building. Having to rewrite this code every single time an object needs to be checked for is cumbersome, and someone will eventually make a mistake.

To get us started, two new parameters will be declared:

DECLARE @SchemaMustContainThisObject VARCHAR(100);
DECLARE @SchemaCannotContainThisObject VARCHAR(100);

When @SchemaMustContainThisObject contains a value, then its presence will be validated and only databases that contain it will have the query executed against them. When @SchemaCannotContainThisObject contains a value, then databases that contain that object will be excluded.

The following is a simple implementation of this logic, looking for the number of schemas that have a Customers table in the WideWorldImporters database:

DECLARE @SchemaMustContainThisObject VARCHAR(100) = 'Customers';
DECLARE @SchemaCannotContainThisObject VARCHAR(100);
DECLARE @DatabaseName sysname = 'WideWorldImporters'
DECLARE @SQL NVARCHAR(MAX);
DECLARE @ObjectValidationCount INT;
DECLARE @ObjectExceptionCount INT;
DECLARE @ParameterList NVARCHAR(MAX);
IF @SchemaMustContainThisObject IS NOT NULL
BEGIN
     SELECT @SQL = 'SELECT @ObjectValidationCount = COUNT(*) FROM ' + 
               QUOTENAME(@DatabaseName) + --in case database name has 
                                          --spaces/special characters
                  '.sys.objects WHERE objects.name = ''' +  
               @SchemaMustContainThisObject + ''';';
     SELECT @ParameterList = '@ObjectValidationCount INT OUTPUT';
     EXEC sp_executesql @SQL, 
                        @ParameterList, 
                        @ObjectValidationCount OUTPUT;
END;
IF @SchemaCannotContainThisObject IS NOT NULL
BEGIN
     SELECT @SQL = 'SELECT @ObjectExceptionCount = COUNT(*) FROM ' + 
            QUOTENAME(@DatabaseName) + 
            '.sys.objects WHERE objects.name = ''' + 
            @SchemaCannotContainThisObject + ''';';
     SELECT @ParameterList = '@ObjectExceptionCount INT OUTPUT';
     EXEC sp_executesql @SQL, 
                        @ParameterList, 
                        @ObjectExceptionCount OUTPUT;
END;
SELECT @ObjectValidationCount AS FoundObjectCount,
       @ObjectExceptionCount AS NotFoundObjectCount;

Executing this code, you will see that there are two schemas in the WideWorldImporters have a Customers object (the one we have been working with, and another a view object in the Website schema).

Additional variables are declared to support parameterized dynamic SQL, as well as to store the output of the test queries. The counts can then be checked later to validate if an object exists or not. Note that sys.objects is used here for convenience without any added object type checks. If additional criteria or object types need to be checked, adding checks on the type_desc column in sys.objects can be used to further filter object details and return exactly what you are looking for. If only tables are checked, then sys.tables could be used instead. Similarly, if there is a need to check multiple types at once, additional variables/parameters could be declared for different object types.

Providing an Explicit Database List

A simple, but common implementation of this is to have an added parameter that provides an explicit database list to run a query against. If it is known exactly which databases need to be executed against and the list will not change without this code being altered, then it is possible to pass in a detailed list. This can be accomplished with a string or table-valued parameter, depending on your preference. Since the number of databases (and the length of this list) is bound by how many databases you have in a single place, it’s not likely that the length of this list would become prohibitively long for either solution. Therefore, the database list may be stored as a comma-separated-values string, like this:

DECLARE @DatabaseList VARCHAR(MAX);
SELECT @DatabaseList = 
          'AdventureWorks2017, BaseballStats, WideWorldImporters';

It may also be stored using a table-valued parameter:

CREATE TYPE dbo.DatabaseListType AS TABLE
     (DatabaseName SYSNAME);
GO
DECLARE @DatabaseList dbo.DatabaseListType;

Similarly, the table variable may be memory-optimized, if your SQL Server version supports it and you are so inclined:

CREATE TYPE dbo.DatabaseListType AS TABLE
     (DatabaseName SYSNAME PRIMARY KEY NONCLUSTERED)
WITH (MEMORY_OPTIMIZED = ON);
GO
DECLARE @DatabaseList dbo.DatabaseListType;

If you choose that option, it is also worth considering using natively compiled stored procedures for consuming memory-optimized table variable. This is not an article about memory-optimized-awesomeness, and therefore a discussion of that will be skipped here. In either case, database names can be inserted into the user-defined table type like this:

INSERT INTO @DatabaseList
        (DatabaseName)
VALUES
     ('AdventureWorks2017'),
     ('BaseballStats'),
     ('WideWorldImporters');

The final step is to prepare the T-SQL to execute and run it against the selected database list. A loop will be used to iterate through each database to run against. Assuming the query to execute is stored in the variable/parameter @SQLCommand, and the name of the current database to execute against is stored in @DatabaseName, then the resulting execution code would look like this:

CREATE TABLE #DatabaseList
        (DatabaseName SYSNAME,
         IsProcessed BIT);
INSERT INTO #DatabaseList
        (DatabaseName, IsProcessed)
--<<<[Database List Determined Above and 0 for IsProcessed]>>>
DECLARE @CurrentDatabaseName SYSNAME,
        @SQLCommand nvarchar(max);
WHILE EXISTS (SELECT * FROM #DatabaseList WHERE IsProcessed = 0)
BEGIN
        SELECT TOP 1
                @CurrentDatabaseName = DatabaseName
        FROM #DatabaseList
        WHERE IsProcessed = 0;
                            -- Replace "?" with the database name
        SELECT @SQLCommand = REPLACE(@SQLCommand, 
                                      '?', @CurrentDatabaseName); 
                           
        EXEC sp_executesql @SQLCommand;
        UPDATE #DatabaseList
                SET IsProcessed = 1
        WHERE DatabaseName = @CurrentDatabaseName;
END;
SELECT *
FROM   #DatabaseList;

Executing this code using the table variable created earlier in the section, you will see that the databases you passed in will be processed (in this snippet of code, it will actually work whether the database exists or not)

There are many ways to write this code, so if your own implementation varies from this and works, then roll with it 😊 The only added feature in there is to replace question marks with the database name. Note that if your query naturally contains question marks, then this may be problematic. This was added to mimic the behavior of sp_msforeachdb and make it easy to insert/use the local database name for each database that is executed against in the loop. If your environment makes common use of question marks in code, then feel free to adjust it to a character or string that is more obscure/deliberate.

Conclusion

Building reusable tools is fun and can provide a great deal of utility in the future if more needs arise that require code like this. A script containing all elements in this article is attached. Feel free to use it, customize it, and provide feedback if you think of new or interesting ways to tweak it.

There is value in avoiding use of undocumented SQL Server features in production environments. Fortunately for this tool, replacing it is not very complicated, and the new version can be given significantly more utility!

The post Running Queries Across Many SQL Server Databases appeared first on Simple Talk.



from Simple Talk https://ift.tt/JMPCYqi
via

Unmasking SQL Server Dynamic Data Masking, Part 5, Mitigations and Summary

This is the fifth and final part of this series on SQL Server Dynamic Data Masking. The first part in the series was a brief introduction to dynamic data masking, completing solutions, and use cases. The second part covered setting up masking and some examples. The third and fourth sections explored side channel attacks against dynamic data masking.

This final part covers mitigations to side channel attacks, additional architectural considerations and an analysis of the overall solution.

Side Channel Attack Mitigations

Now that the reality of side-channel attacks is clear, mitigations for attacks need to be explored. The main foundation of mitigations is that data can’t be attacked if it is not accessible. As shown in previous examples, the attack vector for masked data starts with authorization. The functional nature of masked data, including joins, functions and clauses, requires vulnerability to these side channel attacks. All of the strengths and uses of masked data rely on the data being accessible at some level. If a user is not authorized for the data, they don’t have a foothold to attack the data. A smaller footprint also helps mitigate damage from compromised accounts, as the bad actor will have access to less data and hopefully fewer systems. This is all a pillar of the principle of least privilege.

Rely on your regular user audits and authorization mechanisms to provide access to the data. Only trusted users should be allowed access to data. Even if that data is masked. Most users won’t be savvy enough to unmask the data, but if they get a foothold into the database they will be able to peek under the covers. Data security happens in many layers. It starts with trust. If you don’t trust your users, they shouldn’t have direct access to the data. If they don’t have direct access to the data it is not possible for the users to unmask the data.

Limit Users

The primary defense against side channel attacks is limiting users that have access to your data to those that are trusted. This is clear and straight forward, part of standard security practices. Business users and even business owners don’t always understand the implications of bad security practices, they just want the project to work. You have to be a technical ambassador for the project and for security.

It is very easy to apply the db_datareader built in role to all users but it may not be the best strategy. Security design should be at the forefront of database design, from schema creation, group access, and advanced features like dynamic data masking and RLS. Carefully consider how objects will be accessed during the design phase of the database and project. If you use staging tables or there are pre-processed report tables, remember security on these objects. Pre-processing and staging data is often best done in a separate database. Regular users shouldn’t have access to this database, simplifying the security model.

Another important reminder is that data isn’t masked for from system administrators (sa login/ sysadmin role). You can audit those users and you can lock them down in other ways, such as Row-Level Security (RLS), but the nature of the super-powered roles means there is always a workaround. The workaround can be as simple as assigning security or granting access to additional objects in RLS by performing an insert statement. There is no substitute to trusting your administrators. If an administrator is a bad actor, it is much more difficult to limit damage.

No matter how data is locked down, administrators can always restore a copy to an unmanaged instance and make security changes in that instance that includes no logging. There is also the option of using DBCC PAGE to directly examine data in data files and log files, which could be done directly against a production database.

Locking Down Users

The mitigations discussed assume that the rest of your network is hardened, and users already have to pass through multiple layers to reach your databases. Establishing a good working relationship with the networking and firewall teams is critical to ensuring your database is safe. As previously mentioned, you must also work with the business to determine how the data should be protected. Security is a compromise.

The most secure data is locked in a room and is not accessible by anyone over the network, but that isn’t very usable. Security depends on layers. Procedural, physical, network, active directory, server, database, row level, schema level, etc. One layer of security, including dynamic data masking isn’t sufficient. Each layer works best when it is as transparent as possible and non-intrusive to the end users. When security breaks the flow of work it is less likely to be used. Dynamic data masking is non-intrusive and easy to implement so it meets that criteria. Each layer of security in your solution should meet that same criterion.

Architectural Decisions

Fundamental design decisions impact the data that is potentially exposed via side channel attacks. In additional to user selection and security design, the table architecture and columns configured is a foundational part of limiting exposure. As seen in the proof-of-concept scripts above, it is possible for data to be unmasked.

If exposing sensitive financial data, or any numeric data, it would be more secure rolled up into categories rather than giving access to individual rows. This is a good practice for multiple reasons, in addition to security considerations. Performance, data storage, repeatability are all improved by pre-aggregating the data. If drilldowns to the transaction level data is necessary, consider leaving out the masked column data for those details.

Masking isn’t a good solution for transactional databases and should be used with caution on non-aggregated data. Masking should be used on data that doesn’t expose proprietary business attributes or personally identifiable information. SOX compliant, HIPAA compliant, GDPR compliant, and other systems that must comply with a regulatory body, should use masking carefully and only as part of an architectural design that ensures the security of the data. These systems are typically audited, but that should be verified and implemented if they aren’t currently audited. Also be sure to work with the network and security teams to be sure they are aware of the sensitive nature of the data so they can also monitor for suspicious traffic.

Encryption

Another layer of security to be considered is encryption. Encryption also is available in layers, but doesn’t have to be implemented at each layer. TLS / SSL (on the wire encryption), at rest encryption (detached database files), backup file encryption, column level encryption (many options for this including database key level encryption), client implemented encryption, etc.

Given the nature of most types of encryption in SQL Server, it doesn’t protect against unauthorized unmasking of data. If data is encrypted at a per-client or per-user level, it would protect between clients, but not data within their scope. If data is directly accessible to end users in an unencrypted state, no matter how it is masked, it is potentially vulnerable. It is a matter of priority for a bad actor.

Data that is only unmasked in the client application can still be captured via screenshots. If that is somehow disabled by the administration team, it could still be captured by taking a picture of the screen. There is always a work-around. The principle of least privilege should always be used. And it is always harder to remove access than to grant access. This is true for service accounts as well as user accounts. Removing access after a project / database has been deployed requires regression testing and a thorough understanding of the application. In larger or complex projects, this isn’t a simple matter.

 Monitoring and Audits

Data access should be monitored and regularly audited. Most organizations of size have hundreds if not thousands of databases. It is not possible to constantly monitor these databases manually, custom scripts and audits must be used to make this feasible. Extended events focusing on excessive access to masked columns can be used to find potential unmasking activity. Auditing users with access to unmasked data should also be a common practice. It can be difficult to monitor security configurations on large teams, especially when approvers are not extremely technical.

Verifying that security hasn’t changed and is implemented as expected is an important audit activity. DBA and monitoring tasks are often handled by a different team than the team creating the masking solution. As described above, dynamic data masking requires deep understanding of the project and business rules, which is not possible for DBA teams managing hundreds of servers. It is essential for the teams to work together, especially on systems with sensitive data.

Built in protections / mitigations

In this section, I will outline a couple of the built in protections against invalid data use.

Copying the data out to another table has no impact

If masked data is copied to a new table or extracted, the masking is maintained. This alleviates some concerns about masking and supports the basic functionality of masking. This means that the new table or extract data will have the unmasked data represented as unmasked data, and the masked data will be shown with the same mask as seen in SSMS. The copied data loses the functionality of native masking but preserves the security. If data is extracted via a report, BCP, SSMS, or any other query tool, the masking status is maintained.

If data is put into a new table with a SELECT INTO, masking is maintained. This leaves the primary concern as setting up security correctly and monitoring for any possible breaches. This also questions the basic premise of masking data. If users shouldn’t be allowed to see data in an unmasked stated, why give them access in any form? It is easy, it does not impact existing functionality, and it can be used in any future functionality.

Copying to memory optimized table has no impact. Extractions do not unmask data.

Memory optimized tables and other advanced features have no impact on masking. If data is moved from the masked table to another table or an extract file, the mask is now the actual data. The same is true if the data is moved to a temp table, table variable, or a memory optimized table. If a data column is masked and is then exported, the export will contain your mask. For example, if the default mask was used for a date, all export rows will contain the value 1900-01-01 00:00:00.0000000. This means that mitigations aren’t needed for extracts, inserts, updates or any other standard data movements and manipulations.

System maintained temporal tables (history tables) are another way to potentially access unmasked data. The history table associated with each base table has a copy of each row as it changes in the base table. When a column in the base table is masked, the associated column in the history table is also masked. This table is vulnerable to the same types of side channel attacks but is not vulnerable directly.

Summary and Analysis

It might work best to finalize the discussion of dynamic data masking by describing both what it is and what it is not. Starting with the positives, data masking implements a form of obfuscation. The data is not actually changed rather it is scrambled as it is presented from the data engine. This allows the data engine to perform all of the normal operations on the data with no modifications to queries, including functions and matching in WHERE or ON statements. Because the data is not actually modified it also makes it transparent to existing queries. Data masking is a customizable masking solution built into the engine. This allows the data and the type of obfuscation to be determined by the development team. Data masking is also part of a layered approach to security. It is not a replacement for other security mechanisms, but it is a complementary layer to your current security.

There are potential gaps in the security and it can’t be the only method of making data safe, but it helps. It is also safe at a presentation layer. If used in reports it can be extremely secure. The converse side of that is the database engine level. It is a potential risk if users are allowed direct access to data in the engine. Be sure you know the weaknesses and monitor your database carefully if users are allowed to access databases directly.

The positive list for dynamic data masking makes some of the items on the negatives obvious. It is not encryption. Don’t expect the data to be 100% secure if users have direct access. The obfuscation has some weaknesses that could be exploited by a savvy user. Since it is a possible layer of security, it is not a replacement for other security mechanisms. All security should be implemented as in any other project. And even though it is a layer in a security solution, it is not a traditional security mechanism. Users will still be able to query data that is masked and join to it. There are multiple ways to take advantage of these functional parts of the solution. Another reminder is that dynamic masking is not column level security. Users are still able to query the column, they just get obfuscated data.

Dynamic data masking can be a useful part of a business intelligence solution. Understanding the limitations, strengths, and mitigations to dynamic data masking is an important part of implementing a solution. It’s also important to remember that this is a single part of a security strategy. Security works in layers and this is no exception.

This analysis isn’t meant to castigate Microsoft and their implementation of data masking. To fit existing queries and business requirements, it is a good blend of security and compromise. It is also a good reminder that direct query access and data extracts should only be available to trusted users and partners. There is often a work-around for security protocols if direct access is granted to data. Data security is very similar to physical security and hardware. If you can touch the machine, there is usually a way into it. If you have direct access to the data and enough time, there is usually a way to the base data. Be careful, use multiple layers of defense, and monitor your database activity. Dynamic data masking isn’t a magic bullet to security, but it is a potential layer to help secure data. Each layer should be monitored and each layer should be audited regularly.

 

The post Unmasking SQL Server Dynamic Data Masking, Part 5, Mitigations and Summary appeared first on Simple Talk.



from Simple Talk https://ift.tt/aSVxe20
via